public JsonResult XuLyHuyHDTheoMATT(Common.DefaultObj obj) { var index = 0; try { obj.DataSource = Common.Directories.HDData; obj = getDefaultObj(obj); var source = Common.Directories.HDData + obj.time; var hddttime = obj.datetime.ToString("MMyyyy"); string strUpload = "Cập nhật thành công "; // var file = obj.DataSource + "hoadon.zip"; // // TM.IO.Zip.ExtractZipFile(file, null, obj.DataSource); // FileManagerController.DeleteDirFile(file); var ok = new System.Text.StringBuilder(); // var doc = new System.Xml.XmlDocument(); // file = file.Replace(".zip", ".xml"); // doc.Load(file); var file_hoadonhuy = "hoadonhuy.xml"; var file_hoadonhuy_zip = "hoadonhuy.zip"; // Loại bỏ obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},"; using (System.IO.Stream outfile = new System.IO.FileStream(obj.DataSource + file_hoadonhuy, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite)) { ok.Append("<Inv>"); var tthuy = obj.data_value.Trim().Trim(',').Split(','); foreach (var item in tthuy) { ok.Append($"<key>{item}{hddttime}</key>").AppendLine(); } ok.Append("</Inv>").AppendLine(); outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString())); // outfile.Close(); } //ZipFile if (obj.ckhZipFile) { TM.IO.Zip.ZipFile(new List <string>() { obj.DataSource + file_hoadonhuy }, obj.DataSource + file_hoadonhuy_zip, 9, false); } //Insert To File Manager FileManagerController.InsertFile(obj.DataSource + file_hoadonhuy); FileManagerController.InsertFile(obj.DataSource + file_hoadonhuy_zip); FileManagerController.DeleteDirFile(file_hoadonhuy); return(Json(new { success = $"Tạo hóa đơn hủy thành công! Truy cập File Manager \"~\\{source.Replace("Uploads\\", "")}\" để tải file", url = UrlDownloadFiles($"{source}\\{file_hoadonhuy_zip}") }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); } finally { } }
public JsonResult XuLyHD(Common.DefaultObj obj, bool?ckhExcept) { var ok = new System.Text.StringBuilder(); var err = new System.Text.StringBuilder(); var index = 0; obj.DataSource = Common.Directories.HDData; obj = getDefaultObj(obj); var hddttime = obj.datetime.ToString("MMyyyy"); var FoxPro = new TM.Connection.OleDBF(obj.DataSource); var indexError = ""; // Loại bỏ obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},"; try { var hdall = "hdall" + obj.time; var hdallhddt = "hdall_hddt" + obj.time; TM.OleDBF.DataSource = TM.Common.Directories.HDData + obj.time + "\\"; //Khai báo biến var listKey = new List <string>(); var NumberToLeter = new TM.Helper.NumberToLeter(); var hasError = false; string fileName = "hoadon"; string fileNameDBFHDDT = TM.OleDBF.DataSource + hdallhddt + ".dbf"; string fileNameXMLFull = TM.OleDBF.DataSource + fileName + ".xml"; string fileNameZIPFull = TM.OleDBF.DataSource + fileName + ".zip"; string fileNameXMLError = TM.OleDBF.DataSource + fileName + "_Error.xml"; //Xóa file HDDT cũ FileManagerController.DeleteDirFile(fileNameDBFHDDT); FileManagerController.DeleteDirFile(fileNameZIPFull); FileManagerController.DeleteDirFile(fileNameXMLError); // TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hdall + ".dbf", fileNameDBFHDDT); //Xử lý trùng mã thanh toán khác đơn vị RemoveDuplicate(TM.OleDBF.DataSource, hdallhddt, new string[] { "tong_cd", "tong_dd", "tong_net", "tong_tv", "vat", "tong", "kthue", "giam_tru", "tongcong" }, new string[] { "acc_net", "acc_tv", "so_dd", "so_cd", "diachi_tt", "ma_tuyen", "ms_thue", "ma_dt", "ma_cbt", "banknumber" }, "Remove Duplicate hoadon", null); //Get data from HDDT var data = FoxPro.Connection.Query <Models.HD_DT>($"SELECT * FROM {hdallhddt}").ToList().Trim(); //TM.OleDBF.ToDataTable("SELECT * FROM " + hdallhddt); //HDDT Hoa don using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLFull), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite)) { ok.Append($"<Invoices><BillTime>{obj.time}</BillTime>").AppendLine(); err.Append($"<Invoices><BillTime>{obj.time}</BillTime>").AppendLine(); foreach (var i in data) { index++; indexError = $"Index: {index}, APP_ID: {i.APP_ID}"; #region List Product //<Product> //<ProdName>Dịch vụ Internet</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>1</Amount> //</Product> //<Product> //<ProdName>Dịch vụ MyTv</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>0</Amount> //</Product> //<Product> //<ProdName>Dịch vụ Cố định</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>20000</Amount> //</Product> //<Product> //<ProdName>Dịch vụ Di động</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>0</Amount> //</Product> //<Product> //<ProdName>Khuyến mại, giảm trừ</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>0</Amount> //</Product> #endregion var check = true; var MA_TT = i.MA_TT.Replace("-", ""); //Check EzPay if (i.KIEU_TT == 1) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Is Ezpay</error></Inv>").AppendLine(); check = false; hasError = true; } //Check Error if (string.IsNullOrEmpty(MA_TT)) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Null Or Empty</error></Inv>").AppendLine(); check = false; hasError = true; } else { if (listKey.Contains(MA_TT)) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Trùng mã thanh toán</error></Inv>").AppendLine(); check = false; hasError = true; } } if (ckhExcept.HasValue) { if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT},") < 0) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine(); check = false; hasError = true; } } else { if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT},") < 0) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine(); check = false; hasError = true; } } //Run if (check) { var Products = $"{getProduct("Dịch vụ Internet", i.TONG_NET)}" + $"{getProduct("Dịch vụ MyTv", i.TONG_TV)}" + $"{getProduct("Dịch vụ Cố định", i.TONG_CD)}" + $"{getProduct("Dịch vụ Di động", i.TONG_DD)}" + $"{getProduct("Khuyến mại, giảm trừ", i.GIAM_TRU)}"; //var so_cd = string.IsNullOrEmpty(dt.Rows[i]["so_cd"].ToString().Trim()); //var so_dd = string.IsNullOrEmpty(dt.Rows[i]["so_dd"].ToString().Trim()); //var acc_net = string.IsNullOrEmpty(dt.Rows[i]["acc_net"].ToString().Trim()); //var acc_tv = string.IsNullOrEmpty(dt.Rows[i]["acc_tv"].ToString().Trim()); //var ma_in = dt.Rows[i]["ma_in"].ToString().Trim(); var Invoice = "<Invoice>" + $"<MaThanhToan><![CDATA[{getMaThanhToanHD(hddttime, MA_TT, i.MA_IN == 2 ? Common.HDDT.DetailDiDong : Common.HDDT.DetailCoDinh)}]]></MaThanhToan>" + $"<CusCode><![CDATA[{MA_TT}]]></CusCode>" + $"<CusName><![CDATA[{(obj.ckhTCVN3 ? i.TEN_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></CusName>" + $"<CusAddress><![CDATA[{(obj.ckhTCVN3 ? i.DIACHI_TT.TCVN3ToUnicode() : i.DIACHI_TT)}]]></CusAddress>" + $"<CusPhone><![CDATA[{getCusPhone(i.ACC_NET, i.ACC_TV, i.SO_CD, i.SO_DD)}]]></CusPhone>" + $"<CusTaxCode>{i.MS_THUE}</CusTaxCode>" + $"<PaymentMethod>{"TM/CK"}</PaymentMethod>" + $"<KindOfService>Cước Tháng {obj.month_year_time}</KindOfService>" + $"<Products>{Products}</Products>" + $"<Total>{i.TONG}</Total>" + $"<DiscountAmount>{i.GIAM_TRU}</DiscountAmount>" + "<VATRate>10</VATRate>" + $"<VATAmount>{i.VAT}</VATAmount>" + $"<Amount>{i.TONGCONG}</Amount>" + $"<AmountInWords><![CDATA[{NumberToLeter.DocTienBangChu(i.TONGCONG, "")}]]></AmountInWords>" + $"<PaymentStatus>0</PaymentStatus>" + $"<Extra>{i.MA_CBT};0;0</Extra>" + $"<ResourceCode>{i.MA_CBT}</ResourceCode>" + "</Invoice>"; Invoice = $"<Inv><key>{MA_TT}{hddttime}</key>{Invoice}</Inv>"; ok.Append(Invoice).AppendLine(); listKey.Add(MA_TT); } if (index % 100 == 0) { outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString())); ok = new System.Text.StringBuilder(); } } ok.Append("</Invoices>"); outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString())); // outfile.Close(); } if (hasError) { using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLError), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite)) { err.Append("</Invoices>"); outfile.Write(System.Text.Encoding.UTF8.GetBytes(err.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(err.ToString())); outfile.Close(); } //this.danger("Có lỗi xảy ra Truy cập File Manager \"~\\" + TM.OleDBF.DataSource.Replace("Uploads\\", "") + "\" để tải file"); } //ZipFile if (obj.ckhZipFile) { TM.IO.Zip.ZipFile(new List <string>() { fileNameXMLFull }, fileNameZIPFull, 9); } //Insert To File Manager FileManagerController.DeleteDirFile(fileNameXMLFull); FileManagerController.InsertFile(fileNameZIPFull); FileManagerController.InsertFile(fileNameXMLError); return(Json(new { success = $"Tạo hóa đơn điện tử thành công! Truy cập File Manager \"~\\{TM.OleDBF.DataSource.Replace("Uploads\\", "")}\" để tải file" }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); } finally { FoxPro.Close(); } }
public JsonResult XuLyKH(Common.DefaultObj obj) { var ok = new System.Text.StringBuilder(); var err = new System.Text.StringBuilder(); var index = 0; obj.DataSource = Common.Directories.HDData; obj = getDefaultObj(obj); var FoxPro = new TM.Connection.OleDBF(obj.DataSource); var hddttime = obj.datetime.ToString("MMyyyy"); // Loại bỏ obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},"; try { var listKey = new List <string>(); var hasError = false; var fileName = "cus"; var hdallhddt = "hdall_hddt" + obj.time; var fileNameDBFHDDT = TM.OleDBF.DataSource + hdallhddt + ".dbf"; var fileNameXMLFull = TM.OleDBF.DataSource + fileName + ".xml"; var fileNameZIPFull = TM.OleDBF.DataSource + fileName + ".zip"; var fileNameXMLError = TM.OleDBF.DataSource + fileName + "_Error.xml"; //Xóa file HDDT cũ FileManagerController.DeleteDirFile(fileNameZIPFull); FileManagerController.DeleteDirFile(fileNameXMLError); //Get data from HDDT var data = FoxPro.Connection.Query <Models.HD_DT>($"SELECT * FROM {hdallhddt}").ToList().Trim(); //TM.OleDBF.ToDataTable("SELECT * FROM " + hdallhddt); using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLFull), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite)) { ok.Append("<Customers>").AppendLine(); err.Append("<Customers>").AppendLine(); foreach (var i in data) { index++; var MA_TT = i.MA_TT.Replace("-", ""); #region List Product //<Product> //<ProdName>Dịch vụ Internet</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>1</Amount> //</Product> //<Product> //<ProdName>Dịch vụ MyTv</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>0</Amount> //</Product> //<Product> //<ProdName>Dịch vụ Cố định</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>20000</Amount> //</Product> //<Product> //<ProdName>Dịch vụ Di động</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>0</Amount> //</Product> //<Product> //<ProdName>Khuyến mại, giảm trừ</ProdName> //<ProdUnit></ProdUnit> //<ProdQuantity></ProdQuantity> //<ProdPrice></ProdPrice> //<Amount>0</Amount> //</Product> #endregion //Check EzPay var check = true; if (i.KIEU_TT == 1) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Is Ezpay</error></Inv>").AppendLine(); check = false; hasError = true; } //Check Error if (string.IsNullOrEmpty(MA_TT)) { err.Append($"<Customer><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Null Or Empty HDDT Khách hàng</error></Customer>").AppendLine(); check = false; hasError = true; } else { if (listKey.Contains(MA_TT)) { err.Append($"<Customer><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Trùng mã thanh toán Khách hàng</error></Customer>").AppendLine(); check = false; hasError = true; } } if (!string.IsNullOrEmpty(obj.data_value) && obj.data_value.IndexOf($",{MA_TT}{hddttime},") < 0) { err.Append($"<Inv><app_id>{i.APP_ID}</app_id><key>{MA_TT}</key><error>Loại bỏ</error></Inv>").AppendLine(); check = false; hasError = true; } //Run if (check) { var customer = "<Customer>" + $"<Name><![CDATA[{(obj.ckhTCVN3 ? i.TEN_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></Name>" + $"<Code>{MA_TT}</Code>" + $"<TaxCode>{i.MS_THUE}</TaxCode>" + $"<Address><![CDATA[{(obj.ckhTCVN3 ? i.DIACHI_TT.TCVN3ToUnicode() : i.TEN_TT)}]]></Address>" + "<BankAccountName></BankAccountName>" + "<BankName></BankName>" + $"<BankNumber>{i.BANKNUMBER}</BankNumber>" + "<Email></Email>" + "<Fax></Fax>" + $"<Phone>{getCusPhone(i.ACC_NET, i.ACC_TV, i.SO_DD, i.SO_CD)}</Phone>" + "<ContactPerson></ContactPerson>" + "<RepresentPerson></RepresentPerson>" + "<CusType>0</CusType>" + $"<MaThanhToan><![CDATA[{getMaThanhToanKH(obj.month_year_time, MA_TT, i.MA_IN == 2 ? Common.HDDT.DetailDiDong : Common.HDDT.DetailCoDinh)}]]></MaThanhToan>" + "</Customer>"; ok.Append(customer).AppendLine(); listKey.Add(MA_TT); } if (index % 100 == 0) { outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString())); ok = new System.Text.StringBuilder(); } } ok.Append("</Customers>"); outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString())); // outfile.Close(); } if (hasError) { using (System.IO.Stream outfile = new System.IO.FileStream(TM.IO.FileDirectory.MapPath(fileNameXMLError), System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite)) { err.Append("</Customers>"); outfile.Write(System.Text.Encoding.UTF8.GetBytes(err.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(err.ToString())); outfile.Close(); } //this.danger("Có lỗi xảy ra Truy cập File Manager \"~\\" + TM.OleDBF.DataSource.Replace("Uploads\\", "") + "\" để tải file"); } //ZipFile if (obj.ckhZipFile) { TM.IO.Zip.ZipFile(new List <string>() { fileNameXMLFull }, fileNameZIPFull, 9); } //Insert To File Manager FileManagerController.DeleteDirFile(fileNameXMLFull); FileManagerController.InsertFile(fileNameZIPFull); FileManagerController.InsertFile(fileNameXMLError); //Insert To File Manager hdallhddt FileManagerController.InsertFile(fileNameDBFHDDT); //Delete .BAK FileManagerController.RemoveFileSource(obj.DataSource, false); return(Json(new { success = $"Tạo hóa đơn điện tử thành công! Truy cập File Manager \"~\\{TM.OleDBF.DataSource.Replace("Uploads\\", "")}\" để tải file" }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); } finally { FoxPro.Close(); } }
public JsonResult XuLyHuyHD(Common.DefaultObj obj) { var index = 0; try { var source = Common.Directories.orther; var mapSource = Server.MapPath("~/" + source); string strUpload = "Cập nhật thành công "; var fileUpload = new List <string>(); var upload = UploadBase(mapSource, strUpload, fileUpload, ".zip"); var file = ""; if (fileUpload.Count > 0) //return Json(new { danger = "Không có tệp!" }, JsonRequestBehavior.AllowGet); { if (upload == (int)Common.Objects.ResultCode._extension) { return(Json(new { danger = "Tệp phải định dạng .zip!" }, JsonRequestBehavior.AllowGet)); } else if (upload == (int)Common.Objects.ResultCode._length) { return(Json(new { danger = "Chưa đủ tệp!" }, JsonRequestBehavior.AllowGet)); } file = mapSource + fileUpload[0]; } else { file = mapSource + "hoadon.zip"; } //Xóa file HDDT cũ // FileManagerController.DeleteDirFile(file); var ok = new System.Text.StringBuilder(); // TM.IO.Zip.ExtractZipFile(file, null, mapSource); // var doc = new System.Xml.XmlDocument(); file = file.Replace(".zip", ".xml"); doc.Load(file); var file_hoadonhuy = "hoadonhuy.xml"; var file_hoadonhuy_zip = "hoadonhuy.zip"; // Loại bỏ obj.data_value = string.IsNullOrEmpty(obj.data_value) ? null : $",{obj.data_value.Trim(',')},"; using (System.IO.Stream outfile = new System.IO.FileStream(mapSource + file_hoadonhuy, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite)) { ok.Append("<Inv>"); foreach (System.Xml.XmlNode node in doc.DocumentElement.ChildNodes) { if (node.Name == "Inv") { foreach (System.Xml.XmlNode cnode in node.ChildNodes) { if (cnode.Name == "key") { if (string.IsNullOrEmpty(obj.data_value)) { index++; // string x = cnode.InnerText; ok.Append($"<key>{cnode.InnerText}</key>").AppendLine(); } else { if (obj.data_value.IndexOf($",{cnode.InnerText},") < 0) { index++; // string x = cnode.InnerText; ok.Append($"<key>{cnode.InnerText}</key>").AppendLine(); } } } } } // string text = node.InnerText; //or loop through its children as well } ok.Append("</Inv>").AppendLine(); outfile.Write(System.Text.Encoding.UTF8.GetBytes(ok.ToString()), 0, System.Text.Encoding.UTF8.GetByteCount(ok.ToString())); // outfile.Close(); } //ZipFile if (obj.ckhZipFile) { TM.IO.Zip.ZipFile(new List <string>() { source + file_hoadonhuy }, source + file_hoadonhuy_zip, 9); } //Insert To File Manager FileManagerController.InsertFile(source + file_hoadonhuy); FileManagerController.InsertFile(source + file_hoadonhuy_zip); return(Json(new { success = $"Tạo hóa đơn hủy thành công! Truy cập File Manager \"~\\{source.Replace("Uploads\\", "")}\" để tải file", url = UrlDownloadFiles(source + file_hoadonhuy_zip) }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { danger = $"{ex.Message} - Index: {index}" }, JsonRequestBehavior.AllowGet)); } finally { } }
public JsonResult RemoveDuplicate(ObjAdditionalTools obj) { try { TM.IO.FileDirectory.CreateDirectory(TM.Common.Directories.orther); var file = TM.IO.FileDirectory.Upload(Request.Files, TM.Common.Directories.orther, false, new[] { ".dbf" }); if (file.UploadError().Count > 0) { return(Json(new { danger = "Vui lòng chọn file DBF trước khi thực hiện!" })); } var fileName = file.UploadFileString(); var fileNameFull = TM.Common.Directories.orther + fileName; TM.OleDBF.DataSource = fileNameFull; // var ExtraValueList = new List <string>(); var ExtraValueStr = ""; var ExtraValueStr2 = ""; var ExtraValueStr3 = ""; if (obj.ExtraValue != null) { ExtraValueStr = obj.ExtraValue.Trim().Trim(','); ExtraValueList.AddRange(ExtraValueStr.Split(',').Trim()); foreach (var i in ExtraValueList) { ExtraValueStr2 += $"o.{i}=oc.{i} AND "; ExtraValueStr3 += $"o.{i},"; } ExtraValueStr2 = ExtraValueStr2.Substring(0, ExtraValueStr2.Length - 5); ExtraValueStr3 = ExtraValueStr3.Trim(','); } else { obj.IsExtraValue = false; } // string sql = $"ALTER table {fileName} ADD COLUMN app_id n(10)"; try { TM.OleDBF.Execute(sql); } catch (Exception) { } sql = $"ALTER table {fileName} ADD COLUMN dupe_flag n(2)"; try { TM.OleDBF.Execute(sql); } catch (Exception) { } sql = $"UPDATE {fileName} SET app_id=RECNO()"; TM.OleDBF.Execute(sql); sql = $"UPDATE {fileName} SET dupe_flag=0"; TM.OleDBF.Execute(sql); if (obj.IsExtraValue) { sql = $"UPDATE {fileName} SET dupe_flag=1 WHERE app_id in(SELECT app_id FROM {fileName} o INNER JOIN (SELECT {obj.PrimeryKey},{ExtraValueStr},COUNT(*) AS dupeCount FROM {fileName} GROUP BY {obj.PrimeryKey},{ExtraValueStr} HAVING COUNT(*) > 1) oc ON o.{obj.PrimeryKey}=oc.{obj.PrimeryKey} WHERE {ExtraValueStr2})"; } else { sql = $"UPDATE {fileName} SET dupe_flag=1 WHERE app_id in(SELECT app_id FROM {fileName} o INNER JOIN (SELECT {obj.PrimeryKey},COUNT(*) AS dupeCount FROM {fileName} GROUP BY {obj.PrimeryKey} HAVING COUNT(*) > 1) oc ON o.{obj.PrimeryKey}=oc.{obj.PrimeryKey})"; } TM.OleDBF.Execute(sql); if (obj.IsExtraValue) { sql = $"UPDATE {fileName} SET dupe_flag=2 WHERE app_id IN(SELECT MAX(app_id) FROM {fileName} o INNER JOIN (SELECT {obj.PrimeryKey},{obj.ExtraValue},COUNT(*) AS dupeCount FROM {fileName} GROUP BY {obj.PrimeryKey},{obj.ExtraValue} HAVING COUNT(*) > 1) oc ON o.{obj.PrimeryKey}=oc.{obj.PrimeryKey} WHERE {ExtraValueStr2} GROUP BY {ExtraValueStr3})"; } //sql = $"UPDATE {fileName} SET dupe_flag=2 WHERE app_id IN(SELECT MAX(app_id) FROM {fileName} o INNER JOIN (SELECT {obj.PrimeryKey},{ExtraValueStr},COUNT(*) AS dupeCount FROM {fileName} GROUP BY {obj.PrimeryKey},{ExtraValueStr} HAVING COUNT(*) > 1) oc ON o.{obj.PrimeryKey}=oc.{obj.PrimeryKey} WHERE {ExtraValueStr2})"; else { sql = $"UPDATE {fileName} SET dupe_flag=2 WHERE app_id IN(SELECT MAX(app_id) FROM {fileName} o INNER JOIN (SELECT {obj.PrimeryKey},COUNT(*) AS dupeCount FROM {fileName} GROUP BY {obj.PrimeryKey} HAVING COUNT(*) > 1) oc ON o.{obj.PrimeryKey}=oc.{obj.PrimeryKey} GROUP BY o.{obj.PrimeryKey})"; } TM.OleDBF.Execute(sql); sql = $"DELETE FROM {fileName} WHERE dupe_flag=1"; TM.OleDBF.Execute(sql); sql = $"PACK {fileName}"; TM.OleDBF.Execute(sql); FileManagerController.InsertDirectory(TM.Common.Directories.orther); FileManagerController.InsertFile(TM.OleDBF.DataSource); return(Json(new { success = "Xử lý Thành công!", url = UrlDownloadFiles(TM.OleDBF.DataSource, $"{fileName.ToLower().Replace(".dbf", "")}_RemoveDuplicate.dbf") }, JsonRequestBehavior.AllowGet)); // return DownloadFiles(TM.OleDBF.DataSource, fileName.ToLower().Replace(".dbf", "") + "_RemoveDuplicate.dbf"); } catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); } }
public JsonResult ExportExportCustom(Common.DefaultObj obj) { var OWNER = "TTKD_BKN"; var Oracle = new TM.Connection.Oracle(OWNER); var index = 0; obj.DataSource = Common.Directories.HDData; obj = getDefaultObj(obj); FileManagerController.InsertDirectory(obj.DataSource, false); var FoxPro = new TM.Connection.OleDBF(obj.DataSource); try { var qry = $"SELECT * FROM EXPORT_CUSTOM WHERE ID='{obj.file}'"; var EXPORT_CUSTOM = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry); if (EXPORT_CUSTOM == null) { return(Json(new { success = TM.Common.Language.msgUpdateSucsess }, JsonRequestBehavior.AllowGet)); } if (obj.data_value == Common.Objects.EXPORT_CUSTOM.DEFAULT.ToString()) { var rs = _ExportExportCustom(Oracle, FoxPro, obj, EXPORT_CUSTOM); if (!rs) { return(Json(new { danger = "Lỗi kết xuất, vui lòng thử lại" }, JsonRequestBehavior.AllowGet)); } } else if (obj.data_value == Common.Objects.EXPORT_CUSTOM.MODULES.ToString()) { var tableList = EXPORT_CUSTOM.TABLE_LIST.Trim(',').Split(','); if (tableList.Length < 1) { return(Json(new { warning = "Chưa có kết xuất!" }, JsonRequestBehavior.AllowGet)); } foreach (var item in tableList) { if (string.IsNullOrEmpty(item)) { continue; } qry = $"SELECT * FROM EXPORT_CUSTOM WHERE NAME='{item}' AND TYPE_NAME='{Common.Objects.EXPORT_CUSTOM.DEFAULT}'"; var _export = Oracle.Connection.QueryFirstOrDefault <Models.EXPORT_CUSTOM>(qry); var rs = _ExportExportCustom(Oracle, FoxPro, obj, _export); // if (!string.IsNullOrEmpty(EXPORT_CUSTOM.QUERY_CREATE)) { _ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_CREATE); } if (!string.IsNullOrEmpty(EXPORT_CUSTOM.QUERY_END)) { _ExportQueryExportCustom(FoxPro, EXPORT_CUSTOM.TABLE_NAME, EXPORT_CUSTOM.QUERY_END); } if (!rs) { return(Json(new { danger = "Lỗi kết suất, vui lòng thử lại" }, JsonRequestBehavior.AllowGet)); } } } // FileManagerController.ReExtensionToLower($"{Common.Directories.HDData}{obj.time}"); var fileExport = $"{Common.Directories.HDData}{obj.time}\\{EXPORT_CUSTOM.TABLE_NAME}.dbf"; var fileName = $"{EXPORT_CUSTOM.TABLE_NAME}{obj.datetime.ToString("yyyyMM")}.dbf"; if (new System.IO.FileInfo(Server.MapPath($"~/{fileExport}")).Exists) { return(Json(new { success = "Kết xuất thành công!", url = UrlDownloadFiles(fileExport, fileName) }, JsonRequestBehavior.AllowGet)); } return(Json(new { success = "Kết xuất thành công!" }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { return(Json(new { danger = ex.Message }, JsonRequestBehavior.AllowGet)); } finally { Oracle.Connection.Close(); FoxPro.Connection.Close(); } }
public ActionResult UploadFiles(FormCollection collection) { try { var ckhMerginMonth = collection["ckhMerginMonth"] != null ? true : false; var time = collection["time"].ToString(); //Kiểm tra tháng đầu vào if (ckhMerginMonth) { time = DateTime.Now.AddMonths(-1).ToString("yyyyMM"); } //Declare string hdcd = "hdcd" + time; string hddd = "hddd" + time; string hdnet = "hdnet" + time; string hdtv = "hdtv" + time; //Source //DataSource = Server.MapPath("~/Uploads/Data/"); //TM.IO.FileDirectory.CreateDirectory(DataSource); var DataSource = TM.Common.Directories.HDData; FileManagerController.InsertDirectory(DataSource); var fileNameSource = new List <string>(); var fileName = new List <string>(); var fileSavePath = new List <string>(); var dtMergin = new System.Data.DataTable(); int uploadedCount = 0; if (Request.Files.Count > 0) { string CurrentMonthYear = System.IO.Path.GetFileName(Request.Files[0].FileName).ToLower().Replace(".dbf", "").RemoveWord(); fileNameSource.Add(hdcd + ".dbf"); fileNameSource.Add(hddd + ".dbf"); fileNameSource.Add(hdnet + ".dbf"); fileNameSource.Add(hdtv + ".dbf"); DataSource += time + "/"; TM.OleDBF.DataSource = DataSource; //TM.IO.FileDirectory.CreateDirectory(DataSource); FileManagerController.InsertDirectory(DataSource); //Delete old File //TM.IO.Delete(DataSource, TM.IO.Files(DataSource)); for (int i = 0; i < Request.Files.Count; i++) { var file = Request.Files[i]; if (!file.FileName.IsExtension(".dbf")) { this.danger("Tệp phải định dạng .dbf"); return(RedirectToAction("Index")); } if (!fileNameSource.Contains(System.IO.Path.GetFileName(file.FileName).ToLower())) { this.danger("Sai tên tệp"); //return RedirectToAction("Index"); } if (file.ContentLength > 0) { fileName.Add(System.IO.Path.GetFileName(file.FileName).ToLower()); fileSavePath.Add(TM.IO.FileDirectory.MapPath(DataSource) + fileName[i]); file.SaveAs(fileSavePath[i]); uploadedCount++; FileManagerController.InsertFile(DataSource + fileName[i]); } } var rs = "Tải lên thành công </br>"; foreach (var item in fileName) { rs += item + "<br/>"; } this.success(rs); } else { this.danger("Vui lòng chọn đủ 4 tệp hdcd+MM+yy.dbf, hddd+MM+yy.dbf, hdnet+MM+yy.dbf, hdtv+MM+yy.dbf !"); } } catch (Exception ex) { this.danger(ex.Message); } return(RedirectToAction("Index")); }
public ActionResult Mergin(string time, bool ckhMerginMonth) { try { //Declare var hdall = "hdall" + time; string hdcd = "hdcd" + time; string hddd = "hddd" + time; string hdnet = "hdnet" + time; string hdtv = "hdtv" + time; //Kiểm tra tháng đầu vào if (ckhMerginMonth) { time = DateTime.Now.AddMonths(-1).ToString("yyyyMM"); } //Source var fileNameSource = new List <string>(); var dtMergin = new System.Data.DataTable(); var check_file = 0; fileNameSource.Add(hdcd + ".dbf"); fileNameSource.Add(hddd + ".dbf"); fileNameSource.Add(hdnet + ".dbf"); fileNameSource.Add(hdtv + ".dbf"); //Datasource TM.OleDBF.DataSource = TM.Common.Directories.HDData + time + "\\"; var fileList = TM.IO.FileDirectory.FilesToList(TM.OleDBF.DataSource); foreach (var item in fileList) { if (fileNameSource.Contains(item)) { check_file++; } } if (check_file < 4) { this.danger("Chưa tải đủ tệp!"); return(RedirectToAction("Index")); } //Xóa file hdall cũ FileManagerController.DeleteDirFile(TM.OleDBF.DataSource + hdall + ".dbf"); //Tạo bảng hóa đơn ghép //Mã in //- gộp = 1 //- net = 2 //- tv = 3 //- cd = 4 //- dd = 5 TM.OleDBF.CreateTable(hdall, new Dictionary <string, string>() { { "ma_dvi", "n(10)" }, { "ma_tt", "c(20)" }, { "acc_net", "c(20)" }, { "acc_tv", "c(20)" }, { "so_dd", "c(20)" }, { "so_cd", "c(20)" }, { "ten_tt", "c(100)" }, { "diachi_tt", "c(150)" }, { "dienthoai", "c(20)" }, { "ma_tuyen", "c(10)" }, { "ms_thue", "c(15)" }, { "banknumber", "c(16)" }, { "ma_dt", "n(10)" }, { "ma_cbt", "n(15)" }, { "tong_cd", "n(15)" }, { "tong_dd", "n(15)" }, { "tong_net", "n(15)" }, { "tong_tv", "n(15)" }, { "vat", "n(15,2)" }, { "tong", "n(15)" }, { "kthue", "n(15)" }, { "giam_tru", "n(15)" }, { "tongcong", "n(15)" }, { "stk", "c(100)" }, { "ezpay", "n(1)" }, { "kieu", "c(10)" }, { "ghep", "n(1)" }, { "ma_in", "n(1)" }, { "kieu_tt", "n(1)" }, { "flag", "n(1)" }, { "app_id", "n(10)" }, }); #region old //TM.OleDBF.Execute( // "CREATE TABLE " + hdall + @"( // [Ma_dvi] n(2), // [ma_cq] c(50), // [acc_net] c(50), // [acc_tv] c(50), // [so_dd] c(50), // [so_cd] c(50), // [ten_tb] c(100), // [dia_chi] c(100), // [ma_tuyen] c(50), // [ma_st] c(15), // [banknumber] c(16), // [ma_dt] n(1), // [ma_cbt] n(15), // [tong_cd] n(12), // [tong_dd] n(12), // [tong_net] n(12), // [tong_tv] n(12), // [vat] n(12, 2), // [tong] n(12), // [kthue] n(12), // [giam_tru] n(12), // [tongcong] n(12), // [ezpay] n(1), // [Kieu] c(10), // [ghep] n(1), // [ma_in] n(1), // [flag] n(1), // [app_id] n(10))", hdall); #endregion //insert_hdall //Nhập hóa đơn cố định vào hóa đơn ghép TM.OleDBF.Execute(InsertString(hdall, hdcd, new Dictionary <string, string>() { { "ma_dvi", "dvql_id" }, { "ma_tt", "Ma_kh1" }, { "so_cd", "so_tb" }, { "ten_tt", "ten_cq" }, { "diachi_tt", "dia_chi" }, { "ma_tuyen", "ma_tuyen" }, { "ms_thue", "ma_st" }, { "ma_dt", "ma_dt" }, { "ma_cbt", "ma_cbt" }, { "tong_cd", "tong" }, { "vat", "vat" }, { "tong", "tong" }, { "tongcong", "tong_cuoc" }, { "ezpay", "0" }, { "kieu", "1" }, { "ghep", "0" }, { "ma_in", "4" }, { "flag", "1" }, }), "Insert " + hdcd); //Nhập hóa đơn net vào hóa đơn ghép TM.OleDBF.Execute(InsertString(hdall, hdnet, new Dictionary <string, string>() { { "ma_dvi", "ma_dvi" }, { "ma_tt", "ma_tt" }, { "acc_net", "ma_tb" }, { "ten_tt", "ten_tt" }, { "diachi_tt", "diachi_tt" }, { "dienthoai", "dienthoai" }, { "ma_tuyen", "ma_tuyen" }, { "ms_thue", "ms_thue" }, { "ma_dt", "ma_dt" }, { "ma_cbt", "ma_cbt" }, { "tong_net", "Tong" }, { "vat", "vat" }, { "tong", "tong" }, { "tongcong", "tongcong" }, { "ezpay", "0" }, { "kieu", "1" }, { "ghep", "0" }, { "ma_in", "2" }, { "flag", "1" }, }), "Insert " + hdnet); //Nhập hóa đơn tv vào hóa đơn ghép TM.OleDBF.Execute(InsertString(hdall, hdtv, new Dictionary <string, string>() { { "ma_dvi", "ma_dvi" }, { "ma_tt", "ma_tt" }, { "acc_tv", "ma_tb" }, { "ten_tt", "ten_tt" }, { "diachi_tt", "diachi_tt" }, { "dienthoai", "dienthoai" }, { "ma_tuyen", "ma_tuyen" }, { "ms_thue", "ms_thue" }, { "ma_dt", "ma_dt" }, { "ma_cbt", "ma_cbt" }, { "tong_tv", "Tong" }, { "vat", "vat" }, { "tong", "tong" }, { "tongcong", "tongcong" }, { "ezpay", "0" }, { "kieu", "1" }, { "ghep", "0" }, { "ma_in", "3" }, { "flag", "1" }, }), "Insert " + hdtv); //Nhập hóa đơn di động vào hóa đơn ghép TM.OleDBF.Execute(InsertString(hdall, hddd, new Dictionary <string, string>() { { "ma_dvi", "ma_dvi" }, { "ma_tt", "ma_cq" }, { "so_dd", "so_tb" }, { "ten_tt", "ten_tt" }, { "diachi_tt", "diachi_tt" }, { "ma_tuyen", "ma_tuyen" }, { "ms_thue", "ms_thue" }, { "banknumber", "taikhoan" }, { "ma_dt", "ma_dt" }, { "ma_cbt", "ma_cbt" }, { "tong_dd", "cuoc_cthue" }, { "vat", "thue" }, { "tong", "cuoc_cthue+cuoc_kthue" }, { "kthue", "cuoc_kthue" }, { "giam_tru", "giamtru" }, { "tongcong", "tongcong" }, { "ezpay", "ezpay" }, { "kieu", "1" }, { "ghep", "0" }, { "ma_in", "5" }, { "flag", "1" }, }), "Insert " + hddd); //Update Ma_dt 0 -> 1 TM.OleDBF.Execute($"UPDATE {hdall} SET ma_dt=1 WHERE ma_dt=0", hdall); //Remove tongcong<=1000 TM.OleDBF.Execute($"UPDATE {hdall} SET flag=0 WHERE tongcong<=1000", hdall); //Tạo thêm cột app_id //try //{ // TM.OleDBF.Execute(string.Format("ALTER TABLE {0} ADD COLUMN {1} n(10)", file, app_id), "Tạo cột app_id - " + extraEX); //} //catch (Exception) { } //Cập nhật app_id = Auto Increment //TM.OleDBF.Execute(string.Format("UPDATE {0} SET {1}=RECNO()", hdall, app_id), "Cập nhật app_id = Auto Increment "); //Remove Duplicate hdall //Xử lý trùng mã thanh toán khác đơn vị RemoveDuplicate(TM.OleDBF.DataSource, hdall, new string[] { "tong_cd", "tong_dd", "tong_net", "tong_tv", "vat", "tong", "kthue", "giam_tru", "tongcong" }, new string[] { "acc_net", "acc_tv", "so_dd", "so_cd", "diachi_tt", "ma_tuyen", "ms_thue", "ma_dt", "ma_cbt" }, "Remove Duplicate hoadon", "ma_dvi", "ma_tt", "ma_in=1"); //Cập nhật STK UpdateSTK_MA_DVI(Common.Objects.stk_ma_dvi, hdall); //Update NULL TM.OleDBF.Execute($"UPDATE {hdall} SET ezpay=0 WHERE ezpay is null", hdall); TM.OleDBF.Execute($"UPDATE {hdall} SET tong_cd=0 WHERE tong_cd is null", hdall); TM.OleDBF.Execute($"UPDATE {hdall} SET tong_dd=0 WHERE tong_dd is null", hdall); TM.OleDBF.Execute($"UPDATE {hdall} SET tong_net=0 WHERE tong_net is null", hdall); TM.OleDBF.Execute($"UPDATE {hdall} SET tong_tv=0 WHERE tong_tv is null", hdall); TM.OleDBF.Execute($"UPDATE {hdall} SET kthue=0 WHERE kthue is null", hdall); TM.OleDBF.Execute($"UPDATE {hdall} SET giam_tru=0 WHERE giam_tru is null", hdall); //Remove Bak file var deleteFile = FileManagerController.RemoveFileSource(TM.OleDBF.DataSource); //Add hdall to FileManager FileManagerController.InsertFile(TM.OleDBF.DataSource + hdall + ".dbf"); //Return Download File //return RedirectToAction("DownloadFiles"); this.success("Ghép hóa đơn thành công"); } catch (Exception ex) { this.danger(ex.Message); } return(RedirectToAction("Index")); }
public ActionResult MerginSelf(string time, bool ckhMerginMonth) { try { //Kiểm tra tháng đầu vào if (ckhMerginMonth) { time = DateTime.Now.AddMonths(-1).ToString("yyyyMM"); } //Declare string hdcd = "hdcd" + time; string hddd = "hddd" + time; string hdnet = "hdnet" + time; string hdtv = "hdtv" + time; //Source var fileNameSource = new List <string>(); fileNameSource.Add(hdcd + ".dbf"); fileNameSource.Add(hddd + ".dbf"); fileNameSource.Add(hdnet + ".dbf"); fileNameSource.Add(hdtv + ".dbf"); //Datasource TM.OleDBF.DataSource = TM.Common.Directories.HDData + time + "\\"; //Delete old File //var fileList = TM.IO.FileDirectory.FilesToList(TM.OleDBF.DataSource); //foreach (var item in fileList) // if (!fileNameSource.Contains(item.ToLower())) // FileManagerController.DeleteDirFile(TM.OleDBF.DataSource + item); //TM.IO.FileDirectory.Delete(DataSource + item); // else // check_file++; //var deleteFile = RemoveFileSource(TM.OleDBF.DataSource, ".bak", fileNameSource); var deleteFile = FileManagerController.RemoveFileSource(TM.OleDBF.DataSource); //if (deleteFile.CountException < 4) //{ // //this.danger("Vui lòng chọn đủ 4 tệp hdcd+MM+yy.dbf, hddd+MM+yy.dbf, hdnet+MM+yy.dbf, hdtv+MM+yy.dbf !"); // this.danger("Chưa tải đủ tệp!"); // return RedirectToAction("Index"); //} //Backup File //TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hdcd + ".dbf", TM.OleDBF.DataSource + hdcd + "_old.dbf"); //FileManagerController.InsertFile(TM.OleDBF.DataSource + hdcd + "_old.dbf"); //TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hdnet + ".dbf", TM.OleDBF.DataSource + hdnet + "_old.dbf"); //FileManagerController.InsertFile(TM.OleDBF.DataSource + hdnet + "_old.dbf"); //TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hdtv + ".dbf", TM.OleDBF.DataSource + hdtv + "_old.dbf"); //FileManagerController.InsertFile(TM.OleDBF.DataSource + hdtv + "_old.dbf"); //TM.IO.FileDirectory.Copy(TM.OleDBF.DataSource + hddd + ".dbf", TM.OleDBF.DataSource + hddd + "_old.dbf"); //FileManagerController.InsertFile(TM.OleDBF.DataSource + hddd + "_old.dbf"); //Remove Duplicate RemoveDuplicate(TM.OleDBF.DataSource, hdcd, new string[] { "tong", "vat", "tong_cuoc" }, new string[] { "ten_cq", "dia_chi", "ma_st" }, "Remove Duplicate " + hdcd, "dvql_id", "ma_kh1"); RemoveDuplicate(TM.OleDBF.DataSource, hddd, new string[] { "cuoc_cthue", "thue", "tongcong", "cuoc_kthue", "giamtru" }, new string[] { "ten_tt", "diachi_tt", "ms_thue", "taikhoan" }, "Remove Duplicate " + hddd, "ma_dvi", "ma_cq"); RemoveDuplicate(TM.OleDBF.DataSource, hdnet, new string[] { "tong", "vat", "tongcong" }, new string[] { "ten_tt", "diachi_tt", "ms_thue", "dienthoai" }, "Remove Duplicate " + hdnet, "ma_dvi", "ma_tt"); RemoveDuplicate(TM.OleDBF.DataSource, hdtv, new string[] { "tong", "vat", "tongcong" }, new string[] { "ten_tt", "diachi_tt", "ms_thue", "dienthoai" }, "Remove Duplicate " + hdtv, "ma_dvi", "ma_tt"); ReExtensionToLower(TM.OleDBF.DataSource); //Update HDCD TM.OleDBF.Execute("ALTER TABLE " + hdcd + " ALTER COLUMN Dia_chi char(100)", hdcd); TM.OleDBF.Execute("ALTER TABLE " + hdcd + " ALTER COLUMN Ma_cq char(30)", hdcd); //Update Ezpay HDDD TM.OleDBF.Execute("UPDATE " + hddd + " SET ezpay=0 WHERE ezpay is null", "UPDATE Ezpay " + hddd); //Remove Bak file deleteFile = FileManagerController.RemoveFileSource(TM.OleDBF.DataSource); this.success("Ghép hóa đơn lẻ thành công!"); } catch (Exception ex) { this.danger(ex.Message); } return(RedirectToAction("Index")); }