· 7 years ago · Feb 06, 2019, 07:08 AM
1using System;
2using System.Collections.Generic;
3using System.Text.RegularExpressions;
4using System.Configuration;
5using ExtraSolution;
6using EleWise.ELMA.Logging;
7using EleWise.ELMA.Security.Managers;
8using EleWise.ELMA.Security.Models;
9using Context = EleWise.ELMA.Model.Entities.ProcessContext.P_EditCRMLeadsFromExcel;
10
11namespace EleWise.ELMA.Model.Scripts
12{
13 /// <summary>
14 /// Модуль Ñценариев процеÑÑа "Import CRM Leads from Excel"
15 /// </summary>
16 /// <example>
17 /// <![CDATA[
18 /// >>>>>>>>>>>>>>>Ð’ÐЖÐÐЯ ИÐФОРМÐЦИЯ!!!<<<<<<<<<<<<<<<
19 /// Данный редактор Ñоздан Ð´Ð»Ñ Ñ€Ð°Ð±Ð¾Ñ‚Ñ‹ Ñ PublicAPI.
20 /// PublicAPI предназначен Ð´Ð»Ñ Ñ€Ð°Ð·Ñ€Ð°Ð±Ð¾Ñ‚ÐºÐ¸ Ñценариев ELMA.
21 /// Ðапример, Ñ Ð¿Ð¾Ð¼Ð¾Ñ‰ÑŒÑŽ PublicAPI можно добавить комментарий к документу:
22 /// //Загружаем документ
23 /// var doc = PublicAPI.Docflow.Document.Load(56);
24 /// //ДобавлÑем комментарий
25 /// PublicAPI.Docflow.Document.AddComment(doc, "тут ваш комментарий");
26 ///
27 /// Более подробно про PublicAPI вы можете узнать тут: http://www.elma-bpm.ru/kb/article-642ApiRoot.html
28 ///
29 /// ЕÑли же вам нужна более ÑÐµÑ€ÑŒÑ‘Ð·Ð½Ð°Ñ Ñ€Ð°Ð·Ñ€Ð°Ð±Ð¾Ñ‚ÐºÐ°, выходÑÑ‰Ð°Ñ Ð·Ð° рамки PublicAPI, иÑпользуйте
30 /// Ñторонние редакторы кода, такие как SharpDevelop и VisualStudio.
31 /// Информацию по запуÑку кода в Ñтороннем редакторе вы можете найти тут:
32 /// http://www.elma-bpm.ru/kb/article-837.html
33 /// ]]>
34 /// </example>
35 public partial class P_EditCRMLeadsFromExcel_Scripts : EleWise.ELMA.Workflow.Scripts.ProcessScriptBase<Context>
36 {
37 /// <summary>
38 /// DownloadRecords
39 /// </summary>
40 /// <param name="context">КонтекÑÑ‚ процеÑÑа</param>
41 public virtual void DownloadRecords(Context context)
42 {
43
44 Logger.Log.Error("Log me - Edit");
45
46// string[]  formatDate =  { "dd.MM.yyyy HH.mm.ss", "dd.MM.yyyy HH:mm:ss", "dd.MM.yyyy h:mm:ss", "dd.MM.yyyy  HH.mm.ss", "dd.MM.yyyy", "yyyyMMdd", "yyyyMMdd-HHmmss" };
47//
48// int current_row = 0;
49// // Ñ‚ÐµÐºÑƒÑ‰Ð°Ñ Ñтрока Ñтраницы файла Excel
50// int current_sheet = 0;
51// // Ñ‚ÐµÐºÑƒÑ‰Ð°Ñ Ñтраница. ÐÑƒÐ¼ÐµÑ€Ð°Ñ†Ð¸Ñ Ð½Ð°Ñ‡Ð¸Ð½Ð°ÐµÑ‚ÑÑ Ñ 0
52// int current_column = 0;
53// // Ñ‚ÐµÐºÑƒÑ‰Ð°Ñ ÐºÐ¾Ð»Ð¾Ð½ÐºÐ°
54// int trans_counter = 0;
55// // Ð¿Ð¾Ð·Ð¸Ñ†Ð¸Ñ Ð² текущей транзакции
56// if (context.Binary == null) {
57// throw new Exception (SR.T ("Файл иÑточник не выбран!"));
58// return;
59// }
60// current_row = (int)context.Counter;
61// string filepath_excel = context.Binary.ContentFilePath;
62// var Excelbook = new Workbook (filepath_excel);
63// var Excelsheet = Excelbook.Worksheets ["Sheet1"];
64// while (Excelsheet.Cells [current_row, current_column].StringValue != "" && trans_counter < context.AmountForUpload)
65// {
66// string elma_id = Excelsheet.Cells [current_row, current_column].StringValue;
67// string IsAcive = Excelsheet.Cells [current_row, current_column+1].StringValue;
68// string campaign_id = Excelsheet.Cells [current_row, current_column+2].StringValue;
69// string cif = Excelsheet.Cells [current_row, current_column + 3].StringValue;
70// string cmsid = Excelsheet.Cells [current_row, current_column + 4].StringValue;
71// string fullname = Excelsheet.Cells [current_row, current_column + 5].StringValue;
72// string sizifid = Excelsheet.Cells [current_row, current_column + 6].StringValue;
73// string salarycardacc = Excelsheet.Cells [current_row, current_column + 7].StringValue;
74// string pensioncardacc = Excelsheet.Cells [current_row, current_column + 8].StringValue;
75// string productid = Excelsheet.Cells [current_row, current_column + 9].StringValue;
76// string phone1 = Excelsheet.Cells [current_row, current_column + 10].StringValue;
77// string phone2 = Excelsheet.Cells [current_row, current_column + 11].StringValue;
78// string phone3 = Excelsheet.Cells [current_row, current_column + 12].StringValue;
79// string phone4 = Excelsheet.Cells [current_row, current_column + 13].StringValue;
80// string lead_type = Excelsheet.Cells [current_row, current_column + 14].StringValue;
81// string term_of_product = Excelsheet.Cells [current_row, current_column + 15].StringValue;
82// string status = Excelsheet.Cells [current_row, current_column + 16].StringValue;
83// string comments = Excelsheet.Cells [current_row, current_column + 17].StringValue;
84// string rm_emp_id = Excelsheet.Cells [current_row, current_column + 18].StringValue;
85// string telesales_emp_id = Excelsheet.Cells [current_row, current_column + 19].StringValue;
86// string branch = Excelsheet.Cells [current_row, current_column + 20].StringValue;
87// string priority = Excelsheet.Cells [current_row, current_column + 21].StringValue;
88// string ministry = Excelsheet.Cells [current_row, current_column + 22].StringValue;
89// string dateofbirth = Excelsheet.Cells [current_row, current_column + 23].StringValue;
90// string other = Excelsheet.Cells [current_row, current_column + 24].StringValue;
91//
92// var lead = new KBCRMLead();
93//
94// var leads = EntityManager<KBCRMLead>.Instance.Find("Id = " + GetCorrectPhone(elma_id, "digits"));
95// if (leads.Any())
96// {
97//
98// lead = leads.FirstOrDefault();
99// context.Log += "Contractor found - " + lead.Name + " - " + lead.Id.ToString() + "\n";
100// }
101// else
102// {
103// context.Log += "Contractor New - " + cif + " - " + lead.Id.ToString() + "\n";
104// }
105// lead.IsActive = true;
106// if (!String.IsNullOrEmpty(IsAcive) && IsAcive == "0")
107// lead.IsActive = false;
108//
109// if (!String.IsNullOrEmpty(campaign_id))
110// {
111// var cmpfilter = InterfaceActivator.Create<IKBCRM_CampaignFilter>();
112// cmpfilter.Code = campaign_id.Trim();
113// var cmp = EntityManager<KBCRM_Campaign>.Instance.Find(cmpfilter, null).FirstOrDefault();
114// if (cmp != null)
115// {
116// lead.Campaign = cmp;
117// }
118// }
119//
120// lead.CIF = !String.IsNullOrEmpty(cif) ? cif : "";
121// lead.CMSId = !String.IsNullOrEmpty(cmsid) ? cmsid : "";
122// lead.SIZIFId = !String.IsNullOrEmpty(sizifid) ? sizifid : "";
123// lead.FullName = !String.IsNullOrEmpty(fullname) ? fullname : "";
124// lead.SalaryCardAccount = !String.IsNullOrEmpty(salarycardacc) ? salarycardacc : "";
125// lead.PensionCardAccount = !String.IsNullOrEmpty(pensioncardacc) ? pensioncardacc : "";
126//
127// if (!String.IsNullOrEmpty(productid))
128// {
129// var prdfilter = InterfaceActivator.Create<IKBCRM_ProductFilter>();
130// prdfilter.Code = productid.Trim();
131// var product = EntityManager<KBCRM_Product>.Instance.Find(prdfilter, null).FirstOrDefault();
132// if (product != null)
133// {
134// lead.Product = product;
135// lead.Name = "CIF" + lead.CIF + " CMSId" + lead.CMSId + " " + lead.Product.Name;
136// }
137// }
138//
139// lead.MobileNomber = GetCorrectPhone(phone1, "phone");
140// lead.PhoneNomber = GetCorrectPhone(phone2, "phone");
141// lead.AdditionalNomber1 = GetCorrectPhone(phone3, "phone");
142// lead.AdditionalNomber2 = GetCorrectPhone(phone4, "phone");
143// lead.TermOfProduct = !String.IsNullOrEmpty(term_of_product) ? term_of_product : "";
144// lead.Ministry = !String.IsNullOrEmpty(ministry) ? ministry.Length > 100 ? ministry.Substring(0, 100) : ministry : "";
145// lead.Other = !String.IsNullOrEmpty(other) ? other.Length > 100 ? other.Substring(0, 100) : other : "";
146//
147// if (!String.IsNullOrEmpty(dateofbirth))
148// {
149// lead.DateOfBirth = DateTime.ParseExact(dateofbirth, formatDate, null, DateTimeStyles.None);
150// }
151//
152// if (!String.IsNullOrEmpty(lead_type))
153// {
154// var leadtypefilter = InterfaceActivator.Create<IKBCRM_LeadTypeFilter>();
155// leadtypefilter.Code = lead_type.Trim();
156// var ldtp = EntityManager<KBCRM_LeadType>.Instance.Find(leadtypefilter, null).FirstOrDefault();
157// if (ldtp != null)
158// {
159// lead.Type = ldtp;
160// }
161// }
162//
163// if (!String.IsNullOrEmpty(status))
164// {
165// var statusfilter = InterfaceActivator.Create<KBCRM_LeadStatusFilter>();
166// statusfilter.Code = status.Trim();
167// var stat = EntityManager<KBCRM_LeadStatus>.Instance.Find(statusfilter, null).FirstOrDefault();
168// if (stat != null)
169// {
170// lead.Status = stat;
171// }
172// }
173//
174// lead.Comment = !String.IsNullOrEmpty(comments) ? comments : "";
175//
176// if (!String.IsNullOrEmpty(branch))
177// {
178// var branchflt = InterfaceActivator.Create<KPTL_BranchFilter>();
179// branchflt.BranchCode = branch.Trim();
180// var brnch = EntityManager<KPTL_Branch>.Instance.Find(branchflt, null).FirstOrDefault();
181// if (brnch != null)
182// {
183// lead.Branch = brnch;
184// }
185// }
186//
187// if (!String.IsNullOrEmpty(priority))
188// {
189// int intpriority;
190// bool result = Int32.TryParse(priority, out intpriority);
191// if (result)
192// {
193// lead.Priority = (KBCRM_LeadPriority)intpriority;
194// } else
195// {
196// lead.Priority = (KBCRM_LeadPriority)2;
197// }
198// }
199//
200// if (!String.IsNullOrEmpty(GetCorrectPhone(rm_emp_id,"digits")))
201// {
202//
203// var usr = UserManager.Instance.Find("Id = " + GetCorrectPhone(rm_emp_id,"digits"));
204// if (usr != null && usr.Any())
205// {
206// lead.RMEmployee = (User)usr.FirstOrDefault();
207// }
208// }
209//
210// if (!String.IsNullOrEmpty(GetCorrectPhone(telesales_emp_id,"digits")))
211// {
212// var usr1 = UserManager.Instance.Find("Id = " + GetCorrectPhone(telesales_emp_id,"digits"));
213// if (usr1 != null && usr1.Any())
214// {
215// lead.TeleSaleEmployee = (User) usr1.FirstOrDefault();
216// }
217// }
218//
219// lead.Save();
220//
221// current_row++;
222// trans_counter++;
223//
224// }
225//
226//
227//
228// if (current_row > context.Counter) {
229// context.Counter = current_row;
230// }
231// else {
232// context.IsUploadEnd = true;
233// }
234
235
236 //####################################################################################################
237 var result = Do(context);
238 if (result != "OK")
239 {
240 Logger.Log.Error("Show error to Client = " + result);
241 }
242 else
243 {
244 Logger.Log.Error("Show OK to Client = " + result);
245 }
246 //####################################################################################################
247
248 context.Error = result;
249
250 context.IsUploadEnd = true;
251
252 }
253
254
255
256 public string GetConnectionString()
257 {
258 try
259 {
260
261 var configFilePath = ConfigurationManager.ConnectionStrings["ConfigurationFile"].ConnectionString;
262 var configFileMap = new ExeConfigurationFileMap();
263 configFileMap.ExeConfigFilename = configFilePath;
264 var config = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, ConfigurationUserLevel.None);
265 return config.ConnectionStrings.ConnectionStrings["MainDB"].ConnectionString;
266 }
267 catch (Exception ex)
268 {
269 Logger.Log.Error("Error on GetConnectionString() = " + ex);
270 }
271
272 return string.Empty;
273 }
274
275
276 public string Do(Context context)
277 {
278 Logger.Log.Error("Do starts...");
279
280 string result = string.Empty;
281
282 try
283 {
284
285
286 //###############################################################################################################
287 string dbpath = GetConnectionString(); //ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString; //ConfigurationManager.AppSettings["DBPath"].ToString();
288 string path = context.Binary.ContentFilePath;
289 string tableName = "KBCRMLead";
290 string tableNameTemp = "KBCRMLead_TEMP";
291 string tableNameWorkflow = "M_KBCRMLead_WFIs";
292
293
294 //drop temp table ==========================================================================
295 string query_dropTempTable = @" IF EXISTS (SELECT * FROM sys.tables s WHERE s.name = '" + tableNameTemp + @"' AND DATEDIFF(DAY, s.create_date, GETDATE()) > 0 )
296 drop table " + tableNameTemp + @" ";
297
298 ResponseModel responseModel_dropTempTable = Database.RunNonQuery(dbpath, query_dropTempTable);
299 if (!responseModel_dropTempTable.IsSuccessfull)
300 {
301 return responseModel_dropTempTable.Message;
302 }
303
304 Logger.Log.Error("Database.RunNonQuery(dbpath, query_dropTempTable) finished");
305
306 //===========================================================================================
307
308
309
310
311 //1. create table
312 string query_CreateTable = @" IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='" + tableNameTemp + @"' AND xtype='U')
313 CREATE TABLE KBCRMLead_TEMP(
314 [Id] [bigint] NULL,
315 [Uid] [uniqueidentifier] NULL,
316 [Name] [nvarchar] (200) NULL,
317 [CIF] [nvarchar] (50) NULL,
318 [CMSId] [nvarchar] (50) NULL,
319 [SIZIFId] [nvarchar] (100) NULL,
320 [Campaign] [bigint] NULL,
321 [Product] [bigint] NULL,
322 [FullName] [nvarchar] (200) NULL,
323 [Type] [bigint] NULL,
324 [TermOfProduct] [nvarchar] (200) NULL,
325 [LastCallDate] [datetime] NULL,
326 [NextCallDate] [datetime] NULL,
327 [AppointmentDate] [datetime] NULL,
328 [Status] [bigint] NULL,
329 [RefuseReason] [bigint] NULL,
330 [RMEmployee] [bigint] NULL,
331 [TeleSaleEmployee] [bigint] NULL,
332 [Branch] [bigint] NULL,
333 [Priority] [int] NULL,
334 [Comment] [nvarchar] (max) NULL,
335 [MobileNomber] [nvarchar] (100) NULL,
336 [PhoneNomber] [nvarchar] (100) NULL,
337 [AdditionalNomber1] [nvarchar] (100) NULL,
338 [AdditionalNomber2] [nvarchar] (100) NULL,
339 [ExpireDate] [datetime] NULL,
340 [IsActive] [nvarchar](10) NULL,
341 [SalaryCardAccount] [nvarchar] (50) NULL,
342 [PensionCardAccount] [nvarchar] (50) NULL,
343 [CreationDate] [datetime] NULL,
344 [CreationAuthor] [bigint] NULL,
345 [ChangeDate] [datetime] NULL,
346 [ChangeAuthor] [bigint] NULL,
347 [ContractNumber] [nvarchar] (200) NULL,
348 [History] [nvarchar] (max) NULL,
349 [IssuedLoanAmount] [float] NULL,
350 [LoanIssueDate] [datetime] NULL,
351 [Ministry] [nvarchar] (100) NULL,
352 [DateOfTheBirth] [datetime] NULL,
353 [Other] [nvarchar] (max) NULL,
354 [Channel] [bigint] NULL,
355 [WorkFlowInstances] [bigint] NULL,
356 [LastChangeStatusDate] [datetime] NULL,
357 [PreliminaryAmount] [nvarchar] (max) NULL,
358 [CrossProduct] [nvarchar] (max) NULL,
359 [CrossProductAmount] [float] NULL,
360 [ContractRefNumber] [nvarchar] (max) NULL,
361 [HotCall] [bigint] NULL,
362 [Kupon] [nvarchar] (20) NULL,
363 [CurrentVersion] [varchar] (20) NULL
364 )";
365
366 ResponseModel responseModel_CreateTable = Database.RunNonQuery(dbpath, query_CreateTable);
367 if (!responseModel_CreateTable.IsSuccessfull)
368 {
369 return responseModel_CreateTable.Message;
370 }
371
372 Logger.Log.Error("Database.RunNonQuery(dbpath, query_CreateTable) finished");
373
374
375 //mappings
376 List<MappingModel> mappings = new List<MappingModel> {
377 new MappingModel{ ExcelColumnName = "id", SQLColumnName = "Id" },
378 new MappingModel{ ExcelColumnName = "Uid", SQLColumnName = "Uid" },
379 new MappingModel{ ExcelColumnName = "Name", SQLColumnName = "Name" },
380 new MappingModel{ ExcelColumnName = "CIF", SQLColumnName = "CIF" },
381 new MappingModel{ ExcelColumnName = "CMSId", SQLColumnName = "CMSId" },
382 new MappingModel{ ExcelColumnName = "SIZIFId", SQLColumnName = "SIZIFId" },
383 new MappingModel{ ExcelColumnName = "Campaign", SQLColumnName = "Campaign" },
384 new MappingModel{ ExcelColumnName = "Product", SQLColumnName = "Product" },
385 new MappingModel{ ExcelColumnName = "FullName", SQLColumnName = "FullName" },
386 new MappingModel{ ExcelColumnName = "Type", SQLColumnName = "Type" },
387 new MappingModel{ ExcelColumnName = "TermOfProduct", SQLColumnName = "TermOfProduct" },
388 new MappingModel{ ExcelColumnName = "LastCallDate", SQLColumnName = "LastCallDate" },
389 new MappingModel{ ExcelColumnName = "NextCallDate", SQLColumnName = "NextCallDate" },
390 new MappingModel{ ExcelColumnName = "AppointmentDate", SQLColumnName = "AppointmentDate" },
391 new MappingModel{ ExcelColumnName = "Status", SQLColumnName = "Status" },
392 new MappingModel{ ExcelColumnName = "RefuseReason", SQLColumnName = "RefuseReason" },
393 new MappingModel{ ExcelColumnName = "RMEmployee", SQLColumnName = "RMEmployee" },
394 new MappingModel{ ExcelColumnName = "TeleSaleEmployee", SQLColumnName = "TeleSaleEmployee" },
395 new MappingModel{ ExcelColumnName = "Branch", SQLColumnName = "Branch" },
396 new MappingModel{ ExcelColumnName = "Priority", SQLColumnName = "Priority" },
397 new MappingModel{ ExcelColumnName = "Comment", SQLColumnName = "Comment" },
398 new MappingModel{ ExcelColumnName = "MobileNomber", SQLColumnName = "MobileNomber" },
399 new MappingModel{ ExcelColumnName = "PhoneNomber", SQLColumnName = "PhoneNomber" },
400 new MappingModel{ ExcelColumnName = "AdditionalNomber1", SQLColumnName = "AdditionalNomber1" },
401 new MappingModel{ ExcelColumnName = "AdditionalNomber2", SQLColumnName = "AdditionalNomber2" },
402 new MappingModel{ ExcelColumnName = "ExpireDate", SQLColumnName = "ExpireDate" },
403 new MappingModel{ ExcelColumnName = "IsActive", SQLColumnName = "IsActive" },
404 new MappingModel{ ExcelColumnName = "SalaryCardAccount", SQLColumnName = "SalaryCardAccount" },
405 new MappingModel{ ExcelColumnName = "PensionCardAccount", SQLColumnName = "PensionCardAccount" },
406 new MappingModel{ ExcelColumnName = "CreationDate", SQLColumnName = "CreationDate" },
407 new MappingModel{ ExcelColumnName = "CreationAuthor", SQLColumnName = "CreationAuthor" },
408 new MappingModel{ ExcelColumnName = "ChangeDate", SQLColumnName = "ChangeDate" },
409 new MappingModel{ ExcelColumnName = "ChangeAuthor", SQLColumnName = "ChangeAuthor" },
410 new MappingModel{ ExcelColumnName = "ContractNumber", SQLColumnName = "ContractNumber" },
411 new MappingModel{ ExcelColumnName = "History", SQLColumnName = "History" },
412 new MappingModel{ ExcelColumnName = "IssuedLoanAmount", SQLColumnName = "IssuedLoanAmount" },
413 new MappingModel{ ExcelColumnName = "LoanIssueDate", SQLColumnName = "LoanIssueDate" },
414 new MappingModel{ ExcelColumnName = "Ministry", SQLColumnName = "Ministry" },
415 new MappingModel{ ExcelColumnName = "DateOfTheBirth", SQLColumnName = "DateOfTheBirth" },
416 new MappingModel{ ExcelColumnName = "Other", SQLColumnName = "Other" },
417 new MappingModel{ ExcelColumnName = "Channel", SQLColumnName = "Channel" },
418 new MappingModel{ ExcelColumnName = "WorkFlowInstances", SQLColumnName = "WorkFlowInstances" },
419 new MappingModel{ ExcelColumnName = "LastChangeStatusDate", SQLColumnName = "LastChangeStatusDate" },
420 new MappingModel{ ExcelColumnName = "PreliminaryAmount", SQLColumnName = "PreliminaryAmount" },
421 new MappingModel{ ExcelColumnName = "CrossProduct", SQLColumnName = "CrossProduct" },
422 new MappingModel{ ExcelColumnName = "CrossProductAmount", SQLColumnName = "CrossProductAmount" },
423 new MappingModel{ ExcelColumnName = "ContractRefNumber", SQLColumnName = "ContractRefNumber" },
424 new MappingModel{ ExcelColumnName = "HotCall", SQLColumnName = "HotCall" },
425 new MappingModel{ ExcelColumnName = "Kupon", SQLColumnName = "Kupon" },
426 new MappingModel{ ExcelColumnName = "CurrentVersion", SQLColumnName = "CurrentVersion" },
427 };
428
429
430
431 string currentVersion = DateTime.Now.ToString("yyyyMMddHHmmss");
432
433 string sourceQuery = @"select
434 [id]
435 ,null as [Uid]
436 ,[Name]
437 ,[CIF]
438 ,[CMSId]
439 ,[SIZIFId]
440 ,[Campaign]
441 ,[Product]
442 ,[FullName]
443 ,[Type]
444 ,[TermOfProduct]
445 ,[LastCallDate]
446 ,[NextCallDate]
447 ,[AppointmentDate]
448 ,[Status]
449 ,[RefuseReason]
450 ,[RMEmployee]
451 ,[TeleSaleEmployee]
452 ,[Branch]
453 ,[Priority]
454 ,[Comment]
455 ,[MobileNomber]
456 ,[PhoneNomber]
457 ,[AdditionalNomber1]
458 ,[AdditionalNomber2]
459 ,[ExpireDate]
460 ,[IsActive]
461 ,[SalaryCardAccount]
462 ,[PensionCardAccount]
463 ,[CreationDate]
464 ,[CreationAuthor]
465 ,[ChangeDate]
466 ,[ChangeAuthor]
467 ,[ContractNumber]
468 ,[History]
469 ,[IssuedLoanAmount]
470 ,[LoanIssueDate]
471 ,[Ministry]
472 ,right([DateOfBirth],4) + '-' + left(right([DateOfBirth], 7), 2) + '/' + left([DateOfBirth], 2) as [DateOfTheBirth]
473 ,[Other]
474 ,[Channel]
475 ,[WorkFlowInstances]
476 ,[LastChangeStatusDate]
477 ,[PreliminaryAmount]
478 ,[CrossProduct]
479 ,[CrossProductAmount]
480 ,[ContractRefNumber]
481 ,[HotCall]
482 ,[Kupon]
483 ," + currentVersion + " as [CurrentVersion] from [Sheet1$]";
484
485 //From Excel to DB
486 ResponseModel responseFromExcelToDB = Excel.ReadDataFromExcelAndWriteToDB(path, dbpath, tableNameTemp, mappings, sourceQuery);
487 if (!responseFromExcelToDB.IsSuccessfull)
488 {
489 return responseFromExcelToDB.Message;
490 }
491
492 Logger.Log.Error("Excel.ReadDataFromExcelAndWriteToDB(path, dbpath, tableNameTemp, mappings, sourceQuery); finished");
493
494
495 //Get current user id
496
497 var user = (User)UserManager.Instance.GetCurrentUser();
498
499
500 //insert from select
501 string queryUpdate = @" UPDATE
502 Table_A
503 SET
504 Table_A.[Uid] = Table_A.[Uid]
505 ,Table_A.[Name] = ISNULL(NULLIF(Table_B.[Name], ''), Table_A.[Name])
506 ,Table_A.[CIF] = Table_B.[CIF]
507 ,Table_A.[CMSId] = Table_B.[CMSId]
508 ,Table_A.[SIZIFId] = Table_B.[SIZIFId]
509 ,Table_A.[Campaign] = Table_B.[Campaign]
510 ,Table_A.[Product] = Table_B.[Product]
511 ,Table_A.[FullName] = Table_B.[FullName]
512 ,Table_A.[Type] = Table_B.[Type]
513 ,Table_A.[TermOfProduct] = Table_B.[TermOfProduct]
514 ,Table_A.[LastCallDate] = ISNULL(NULLIF(Table_B.[LastCallDate], ''), Table_A.[LastCallDate])
515 ,Table_A.[NextCallDate] = ISNULL(NULLIF(Table_B.[NextCallDate], ''), Table_A.[NextCallDate])
516 ,Table_A.[AppointmentDate] = ISNULL(NULLIF(Table_B.[AppointmentDate], ''), Table_A.[AppointmentDate])
517 ,Table_A.[Status] = ISNULL(NULLIF(Table_B.[Status], ''), Table_A.[Status])
518 ,Table_A.[RefuseReason] = ISNULL(NULLIF(Table_B.[RefuseReason], ''), Table_A.[RefuseReason])
519 ,Table_A.[RMEmployee] = Table_B.[RMEmployee]
520 ,Table_A.[TeleSaleEmployee] = Table_B.[TeleSaleEmployee]
521 ,Table_A.[Branch] = ISNULL(NULLIF(Table_B.[Branch], ''), Table_A.[Branch])
522 ,Table_A.[Priority] = Table_B.[Priority]
523 ,Table_A.[Comment] = Table_B.[Comment]
524 ,Table_A.[MobileNomber] = LEFT(Table_B.[MobileNomber], 30)
525 ,Table_A.[PhoneNomber] = LEFT(Table_B.[PhoneNomber], 30)
526 ,Table_A.[AdditionalNomber1] = LEFT(Table_B.[AdditionalNomber1], 30)
527 ,Table_A.[AdditionalNomber2] = LEFT(Table_B.[AdditionalNomber2], 30)
528 ,Table_A.[ExpireDate] = ISNULL(NULLIF(Table_B.[ExpireDate], ''), Table_A.[ExpireDate])
529 ,Table_A.[IsActive] = Table_B.[IsActive]
530 ,Table_A.[SalaryCardAccount] = Table_B.[SalaryCardAccount]
531 ,Table_A.[PensionCardAccount] = Table_B.[PensionCardAccount]
532 ,Table_A.[CreationDate] = ISNULL(NULLIF(Table_B.[CreationDate], ''), Table_A.[CreationDate])
533 ,Table_A.[CreationAuthor] = ISNULL(NULLIF(Table_B.[CreationAuthor], ''), Table_A.[CreationAuthor])
534 ,Table_A.[ChangeDate] = ISNULL(NULLIF(Table_B.[ChangeDate], ''), Table_A.[ChangeDate])
535 ,Table_A.[ChangeAuthor] = ISNULL(NULLIF(Table_B.[ChangeAuthor], ''), Table_A.[ChangeAuthor])
536 ,Table_A.[ContractNumber] = ISNULL(NULLIF(Table_B.[ContractNumber], ''), Table_A.[ContractNumber])
537 ,Table_A.[History] = ISNULL(NULLIF(Table_B.[History], ''), Table_A.[History])
538 ,Table_A.[IssuedLoanAmount] = ISNULL(NULLIF(Table_B.[IssuedLoanAmount], ''), Table_A.[IssuedLoanAmount])
539 ,Table_A.[LoanIssueDate] = ISNULL(NULLIF(Table_B.[LoanIssueDate], ''), Table_A.[LoanIssueDate])
540 ,Table_A.[Ministry] = Table_B.[Ministry]
541 ,Table_A.[DateOfBirth] = Table_B.[DateOfTheBirth]
542 ,Table_A.[Other] = Table_B.[Other]
543 ,Table_A.[Channel] = ISNULL(NULLIF(Table_B.[Channel], ''), Table_A.[Channel])
544 ,Table_A.[LastChangeStatusDate] = ISNULL(NULLIF(Table_B.[LastChangeStatusDate], ''), Table_A.[LastChangeStatusDate])
545 ,Table_A.[PreliminaryAmount] = ISNULL(NULLIF(Table_B.[PreliminaryAmount], ''), Table_A.[PreliminaryAmount])
546 ,Table_A.[CrossProduct] = ISNULL(NULLIF(Table_B.[CrossProduct], ''), Table_A.[CrossProduct])
547 ,Table_A.[CrossProductAmount] = ISNULL(NULLIF(Table_B.[CrossProductAmount], ''), Table_A.[CrossProductAmount])
548 ,Table_A.[ContractRefNumber] = ISNULL(NULLIF(Table_B.[ContractRefNumber], ''), Table_A.[ContractRefNumber])
549 ,Table_A.[HotCall] = ISNULL(NULLIF(Table_B.[HotCall], ''), Table_A.[HotCall])
550 ,Table_A.[Kupon] = ISNULL(NULLIF(Table_B.[Kupon], ''), Table_A.[Kupon])
551
552 FROM
553 " + tableName + @" AS Table_A
554 INNER JOIN
555 (
556 SELECT
557 T.[id] AS [Id]
558 ,newId() AS [Uid]
559 ,'CIF ' + ISNULL(T.[CIF], '') + ' CMSId ' + ISNULL(T.[CMSId], '') + ' ' + ISNULL(P.[Name], '') AS [Name]
560 ,T.[CIF]
561 ,T.[CMSId]
562 ,T.[SIZIFId]
563 ,C.[Id] AS [Campaign]
564 ,P.[Id] AS [Product]
565 ,T.[FullName]
566 ,Y.[Id] AS [Type]
567 ,T.[TermOfProduct]
568 ,T.[LastCallDate]
569 ,T.[NextCallDate]
570 ,T.[AppointmentDate]
571 ,S.[Id] AS [Status]
572 ,T.[RefuseReason]
573 ,T.[RMEmployee]
574 ,ISNULL(NULLIF(T.[TeleSaleEmployee], ''), 1) AS [TeleSaleEmployee]
575 ,B.[Id] AS [Branch]
576 ,T.[Priority]
577 ,T.[Comment]
578 ,T.[MobileNomber]
579 ,T.[PhoneNomber]
580 ,T.[AdditionalNomber1]
581 ,T.[AdditionalNomber2]
582 ,T.[ExpireDate]
583 ,T.[IsActive]
584 ,T.[SalaryCardAccount]
585 ,T.[PensionCardAccount]
586 ,GETDATE() AS [CreationDate]
587 , " + user.Id.ToString() + @" AS [CreationAuthor]
588 ,T.[ChangeDate]
589 ,T.[ChangeAuthor]
590 ,T.[ContractNumber]
591 ,T.[History]
592 ,T.[IssuedLoanAmount]
593 ,T.[LoanIssueDate]
594 ,T.[Ministry]
595 , convert(datetime, T.[DateOfTheBirth], 103) AS [DateOfTheBirth]
596 ,T.[Other]
597 ,T.[Channel]
598 ,T.[LastChangeStatusDate]
599 ,T.[PreliminaryAmount]
600 ,T.[CrossProduct]
601 ,T.[CrossProductAmount]
602 ,T.[ContractRefNumber]
603 ,T.[HotCall]
604 ,T.[Kupon]
605 from " + tableNameTemp + @" T
606
607 LEFT JOIN DBO.KBCRM_Campaign C ON C.[Code] = T.[Campaign]
608 LEFT JOIN DBO.KBCRM_Product p ON P.[Code] = T.[Product]
609 LEFT JOIN DBO.KBCRM_LeadType Y ON Y.[Code] = T.[Type]
610 LEFT JOIN DBO.KBCRM_LeadStatus S ON S.[Code] = T.[Status]
611 LEFT JOIN DBO.KPTL_Branch B ON B.[BranchCode] = T.[Branch]
612
613 WHERE CurrentVersion = '" + currentVersion + @"'
614
615 ) Table_B
616 ON Table_A.Id = Table_B.Id ";
617
618
619 ResponseModel responseModelInsert = Database.RunNonQuery(dbpath, queryUpdate);
620 if (!responseModelInsert.IsSuccessfull)
621 {
622 return responseModelInsert.Message;
623 }
624
625
626 Logger.Log.Error("Database.RunNonQuery(dbpath, queryUpdate); finished");
627
628
629
630 //insert WFI from select
631 string queryUpdateWFI = @" UPDATE
632 Table_WFI
633 SET
634 Table_WFI.Child = T.WorkFlowInstances
635
636 FROM
637 " + tableNameWorkflow + @" AS Table_WFI
638 LEFT JOIN KBCRMLead_TEMP T ON T.Id = Table_WFI.Parent
639 WHERE T.WorkFlowInstances IS NOT NULL AND T.WorkFlowInstances <>'' AND T.CurrentVersion = '" + currentVersion + @"'";
640
641
642 ResponseModel responseModelInsertWFi = Database.RunNonQuery(dbpath, queryUpdateWFI);
643 if (!responseModelInsertWFi.IsSuccessfull)
644 {
645 return responseModelInsertWFi.Message;
646 }
647
648
649
650 Logger.Log.Error("Database.RunNonQuery(dbpath, queryUpdateWFI); finished");
651
652
653 } catch (Exception ex)
654 {
655 Logger.Log.Error("Error on DO = " + ex.ToString());
656 return ex.Message;
657 }
658
659 Logger.Log.Error("Do is OK");
660 return "OK";
661
662 Logger.Log.Error("Do finished!");
663 }
664
665
666 /// <summary>
667 /// GetDigits
668 /// </summary>
669 /// <param name="context">КонтекÑÑ‚ процеÑÑа</param>
670 public string GetCorrectPhone(string phoneNumber, string param)
671 {
672 if (!string.IsNullOrEmpty(phoneNumber))
673 {
674 var regex = new Regex(@"\D");
675
676 var onlyDigits = regex.Replace(phoneNumber, "");
677
678 if (param == "phone")
679 {
680 if (onlyDigits.Length <= 10)
681 {
682 if (onlyDigits.Length == 9)
683 return "0" + onlyDigits;
684 else
685 return onlyDigits;
686 }
687
688 else
689 return "0" + onlyDigits.Substring(onlyDigits.Length - 9, 9);
690 }
691 else
692 {
693 return onlyDigits;
694 }
695
696 }
697 return "";
698 }
699 }
700}