· 8 years ago · Feb 16, 2018, 05:42 PM
1public class CashRegister
2{
3 private static CashRegister CashRegisterInstance;
4
5 public int? Id { get; set; }
6 public string Name { get; set; }
7 public string Description { get; set; }
8 public string DgiSucCode { get; set; }
9 public bool? ForceMoneyControl { get; set; }
10 public bool? Active { get; set; }
11 public Entities.Printer Printer { get; set; }
12 public Entities.Localidad Localidad { get; set; }
13 public List<Entities.User> User { get; set; }
14 public List<Entities.DocumentModel> DocumentModel { get; set; }
15}
16
17/// <summary>
18 /// Create or Update a User Data
19 /// </summary>
20 /// <param name="Data"></param>
21 /// <returns></returns>
22 public Entities.CashRegister Save(Entities.CashRegister Data)
23 {
24 try
25 {
26 using (SqlCommand cmd = new SqlCommand("spCashRegisters_SaveUpdate", Connection))
27 {
28 cmd.CommandType = System.Data.CommandType.StoredProcedure;
29
30 cmd.Parameters.Add(new SqlParameter("@Id", Data.Id));
31 cmd.Parameters.Add(new SqlParameter("@Name", Data.Name));
32 cmd.Parameters.Add(new SqlParameter("@Description", Data.Description));
33 cmd.Parameters.Add(new SqlParameter("@DgiSucCode", Data.DgiSucCode));
34 cmd.Parameters.Add(new SqlParameter("@ForceMoneyControl", Data.ForceMoneyControl));
35 cmd.Parameters.Add(new SqlParameter("@Active", Data.Active));
36 cmd.Parameters.Add(new SqlParameter("@LocalidadId", Data.Localidad.Id));
37 cmd.Parameters.Add(new SqlParameter("@DefaultPrinterID", Data.Printer.id));
38 cmd.Parameters.Add(new SqlParameter("@Users", this.ConvertToStringArray(Data.User)));
39 cmd.Parameters.Add(new SqlParameter("@DocumentModel", this.ConvertToStringArray(Data.DocumentModel)));
40
41 //Agregado para recibir el error en caso de que halla
42 SqlParameter RuturnValue = new SqlParameter("@RequestStatus", SqlDbType.Int);
43 RuturnValue.Direction = ParameterDirection.Output;
44 cmd.Parameters.Add(RuturnValue);
45 /// Fin de Agregado
46 using (SqlDataReader reader = cmd.ExecuteReader())
47 {
48 while (reader.Read())
49 {
50 Data.Id = Convert.ToInt32(reader[0]);
51 }
52 }
53 int retval = (int)cmd.Parameters["@RequestStatus"].Value;
54 Data.Id = retval;
55 }
56
57 return Data;
58 }
59 catch (SqlException ex)
60 {
61 log.Error(ex);
62 throw ex;
63 }
64 catch (Exception ex)
65 {
66 log.Error(ex);
67 throw ex;
68 }
69 }
70
71private string ConvertToStringArray(object o)
72 {
73 List< Entities.DocumentModel> doc = new List<Entities.DocumentModel>();
74 List<Entities.User> us = new List<User>();
75 string returned = "";
76 try
77 {
78 doc = (List< Entities.DocumentModel>)o;
79 var last = doc.Last();
80 foreach (var item in doc)
81 {
82 if (item.Equals(last))
83 {
84 returned = returned + (item.Id).ToString();
85 }else
86 {
87 returned = (item.Id).ToString() + ",";
88 }
89
90 }
91 }
92 catch (Exception)
93 {
94 us = (List<Entities.User>)o;
95 var last = us.Last();
96 foreach (var item in us)
97 {
98 if (item.Equals(last))
99 {
100 returned = returned + (item.Id).ToString();
101 }
102 else
103 {
104 returned = (item.Id).ToString() + ",";
105 }
106
107 }
108 }
109 return returned;
110 }
111
112-- Create or Update a Message
113IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spCashRegisters_SaveUpdate')
114 DROP PROCEDURE [dbo].[spCashRegisters_SaveUpdate]
115GO
116CREATE PROCEDURE [dbo].[spCashRegisters_SaveUpdate] (
117 @Id int = null
118 ,@Name varchar(129) = null
119 ,@Description varchar(256) = null
120 ,@DefaultPrinterID int = null
121 ,@LocalidadId int = null
122 ,@DgiSucCode varchar(10) = null
123 ,@ForceMoneyControl bit = null
124 ,@Active bit = null
125 ,@DocumentModel nvarchar(max) = null
126 ,@Users nvarchar(max) = null
127 ,@RequestStatus int output
128)
129AS
130set @RequestStatus = 1 --Lo seteo en uno en caso de que todo salga bien
131If NOT Exists(select top(1) ID from ApplicaAccCS_Max.dbo.Localidades where ID = @LocalidadId )
132 BEGIN
133 --RAISERROR('Localidad: ingresada no existe: Error en @LocalidadId', 16, 1);
134 set @RequestStatus = -2
135 return -2; ---Localidad Error = -2
136 END
137If NOT Exists(select top(1) pri.Id from Printer as pri where pri.Id = @DefaultPrinterID )
138 BEGIN
139 set @RequestStatus = -4
140 --RAISERROR('Impresora: ingresada no existe: Error en @DefaultPrinterID', 16, 1);
141 return; ---impresora Error = -4
142 END
143 If NOT Exists(select top(1) doc.Id from tbDocumentModels as doc where doc.Id = @DocumentModel )
144 BEGIN
145 --RAISERROR('Documento: ingresado no existe: Error en @@DocumentModelId', 16, 1);
146 set @RequestStatus = -5
147 return -5; ---Documento Error = -5
148 END
149BEGIN
150 IF @id IS NULL BEGIN
151 declare @Id_Ultimo int = 0;
152 -- Insert statements for procedure here
153 begin Transaction
154 begin Try
155 INSERT INTO [dbo].[tbCashRegister]
156 (
157 [Name]
158 ,[Description]
159 ,[DefaultPrinterID]
160 ,[LocalidadId]
161 ,[DgiSucCode]
162 ,[ForceMoneyControl]
163 ,[Active]
164 )
165 VALUES
166 (
167 @Name
168 ,@Description
169 ,@DefaultPrinterID
170 ,@LocalidadId
171 ,@DgiSucCode
172 ,@ForceMoneyControl
173 ,@Active
174 );
175 Select @@IDENTITY;
176 SELECT @Id_Ultimo = SCOPE_IDENTITY()
177
178 INSERT INTO tbUserByCashRegisters(CashRegisterId, UserId)
179 Select @Id_Ultimo
180 ,r.Name
181 from [dbo].[splitstring](@Users) r
182 where Name != ''
183 INSERT INTO tbDocumentModelByCashRegisters(DocumentModelId, CashRegisterId)
184 Select r.Item
185 ,@Id_Ultimo
186 from [dbo].[SplitInts](@DocumentModel, ',') r
187 where Item != ''
188 Commit
189 End Try
190 begin Catch
191 rollback
192 End Catch
193 END
194 ELSE BEGIN
195 -- Insert statements for procedure here
196 begin Transaction
197 begin Try
198 UPDATE [dbo].[tbCashRegister]
199 SET
200 [Name] = @Name
201 ,[Description] = @Description
202 ,[DefaultPrinterID] = @DefaultPrinterID
203 ,[LocalidadId] = @LocalidadId
204 ,[DgiSucCode] = @DgiSucCode
205 ,[ForceMoneyControl]= @ForceMoneyControl
206 ,[Active]= @Active
207 WHERE
208 [Id] = @Id;
209 Select @Id;
210 --UPDATE tbUserByCashRegisters
211 --SET
212 -- [UserId] = @UserId
213 -- ,[CashRegisterId] = @Id
214 --WHERE
215 -- [CashRegisterId] = @Id
216 --UPDATE [dbo].[tbDocumentModelByCashRegisters]
217 --SET
218 -- [DocumentModelId] = @DocumentModelId
219 -- ,[CashRegisterId] = @Id
220
221 --WHERE
222 -- [CashRegisterId] = @Id
223 Commit
224 End Try
225 begin Catch
226 rollback
227 End Catch
228 END
229END
230GO
231
232USE [Applica.Acc]
233 GO
234 /****** Object: UserDefinedFunction [dbo].[splitstring] Script Date: 16/02/2018 12:55:08 ******/
235 SET ANSI_NULLS ON
236 GO
237 SET QUOTED_IDENTIFIER ON
238 GO
239 ALTER FUNCTION [dbo].[splitint] ( @stringToSplit nvarchar(MAX) )
240 RETURNS
241 @returnList TABLE ([id] [int])
242 AS
243 BEGIN
244
245 DECLARE @id int
246 DECLARE @pos INT
247
248 WHILE CHARINDEX(',', @stringToSplit) > 0
249 BEGIN
250 SELECT @pos = CHARINDEX(',', @stringToSplit)
251 SELECT @id = CAST(SUBSTRING(@stringToSplit, 1, @pos-1) as int)
252
253 INSERT INTO @returnList
254 SELECT @id AS [int]
255
256 SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
257 END
258
259 INSERT INTO @returnList
260 SELECT @stringToSplit
261
262 RETURN
263 END
264
265CREATE FUNCTION dbo.SplitInts
266 (
267 @List VARCHAR(MAX),
268 @Delimiter VARCHAR(255)
269 )
270 RETURNS TABLE
271 AS
272 RETURN (
273 SELECT Item = CONVERT(INT, Item) FROM
274 ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
275 FROM
276 ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
277 WHERE Item IS NOT NULL
278 );
279 GO