private void ReadFileMasterData()
{
string destDirectory = Constant.PATH_HISTORY_MASTERDATA;
string pathDirectory = Constant.PATH_MASTERDATA;
string fileName = "";
string path = "";
string[] supplierFiles = Directory.GetFiles(pathDirectory, "*.DAT");
for (int i = 0; i < supplierFiles.Count(); i++)
{
path = supplierFiles[i];
string[] allText = File.ReadAllLines(path);
int numberRow = allText.Count();
txtInform1.AppendText(string.Format(numberRow.ToString()));
int countRowSuccess = 0;
fileName = path.Substring(pathDirectory.Length + 1);
ResultDatabase_Model result = new ResultDatabase_Model();
foreach (string text in allText)
{
result = CountLineSuccess_MasterData(text, countRowSuccess, fileName);
countRowSuccess = result.numberRowSuccess;
txtInform1.AppendText("\n import thành công " + string.Format(countRowSuccess.ToString()) + "\n");
}
if (numberRow == countRowSuccess)
{
this.MoveFile(pathDirectory, destDirectory, fileName, path);
}
}
}
private ResultDatabase_Model CountLineSuccess_MasterData(string text, int count, string fileName)
{
int indicator = DAL.IntReturn(text.Substring(0, 1));
ResultDatabase_Model result = new ResultDatabase_Model();
SupplierController supplierControl = new SupplierController();
ArticlesController_North articleControl = new ArticlesController_North();
if (indicator == 1 || indicator == 2)
{
result = supplierControl.InsertDatabase(text, indicator, fileName);
result.indicator = indicator;
}
else if (indicator == 3 || indicator == 4)
{
result = articleControl.InsertDatabase(text, indicator, fileName);
result.indicator = indicator;
}
if (result.check == true)
{
count++;
result.numberRowSuccess = count;
}
else
{
result.numberRowSuccess = count;
}
return(result);
}
public Boolean Add(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Action_Code", SqlDbType.Char, 1),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 1),
new SqlParameter("@Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Barcode", SqlDbType.Char, 14),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 6),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Barcode_Sequence_Number", SqlDbType.Int, 4),
new SqlParameter("@Editdate", SqlDbType.DateTime, 8),
new SqlParameter("@AddDate", SqlDbType.DateTime, 8),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 200),
};
sp[0].Value = "Addnew";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Action_Code;
sp[4].Value = this.Lsp_Identification;
sp[5].Value = this.Article_Number;
sp[6].Value = this.Barcode;
sp[7].Value = this.Warehouse_Number;
sp[8].Value = this.Date_Record;
sp[9].Value = this.Barcode_Sequence_Number;
sp[10].Value = this.Editdate;
sp[11].Value = this.AddDate;
sp[12].Value = this.FILENAME;
DAL.excutequery(PP_MM_Barcode_Articles, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "Barcode_Article";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Barcode, category);
return(false);
}
}
private ResultDatabase_Model CountLineSuccess_SO(string text, int count, string fileName)
{
int indicator = DAL.IntReturn(text.Substring(0, 1));
ResultDatabase_Model result = new ResultDatabase_Model();
SOController_North soControl = new SOController_North();
result = soControl.InsertDatabase(text, indicator, fileName);
result.indicator = indicator;
if (result.check == true)
{
count++;
result.numberRowSuccess = count;
}
else
{
result.numberRowSuccess = count;
}
return(result);
}
public Boolean Update(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Action_Code", SqlDbType.Char, 1),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 1),
new SqlParameter("@Supplier_Code", SqlDbType.Char, 6),
new SqlParameter("@Address_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Contact_Person", SqlDbType.Char, 35),
new SqlParameter("@Building", SqlDbType.Char, 28),
new SqlParameter("@Address", SqlDbType.Char, 28),
new SqlParameter("@Town", SqlDbType.Char, 25),
new SqlParameter("@Phone", SqlDbType.Char, 15),
new SqlParameter("@Fax_Number", SqlDbType.Char, 18),
new SqlParameter("@Postal_Code", SqlDbType.Char, 11),
new SqlParameter("@DC_Phone_Number", SqlDbType.Char, 15),
new SqlParameter("@Origin", SqlDbType.Char, 4),
new SqlParameter("@Supplier_Repressentative", SqlDbType.Char, 35),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 5),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Email_Address", SqlDbType.Char, 100),
new SqlParameter("@Editdate", SqlDbType.DateTime, 8),
new SqlParameter("@BLANK1", SqlDbType.Char, 7),
new SqlParameter("@BLANK2", SqlDbType.Char, 11),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 200),
};
sp[0].Value = "Update";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Action_Code;
sp[4].Value = this.Lsp_Identification;
sp[5].Value = this.Supplier_Code;
sp[6].Value = this.Address_Indicator;
sp[7].Value = this.Contact_Person;
sp[8].Value = this.Building;
sp[9].Value = this.Address;
sp[10].Value = this.Town;
sp[11].Value = this.Phone;
sp[12].Value = this.Fax_Number;
sp[13].Value = this.Postal_Code;
sp[14].Value = this.DC_Phone_Number;
sp[15].Value = this.Origin;
sp[16].Value = this.Supplier_Repressentative;
sp[17].Value = this.Warehouse_Number;
sp[18].Value = this.Date_Record;
sp[19].Value = this.Email_Address;
sp[20].Value = this.Editdate;
sp[21].Value = this.BLANK1;
sp[22].Value = this.BLANK2;
sp[23].Value = this.FILENAME;
DAL.excutequery(PP_MM_Supplier_Detail, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "SUPPLIER_DETAIL";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Supplier_Code, category);
return(false);
}
}
public ResultDatabase_Model InsertDatabase(string text, int indicator, string fileName)
{
ResultDatabase_Model result = new ResultDatabase_Model();
Response_ReadFile error = new Response_ReadFile();
bool check = false;
if (indicator == 1)
{
ClsMM_Orders_Header orderHeader = new ClsMM_Orders_Header();
try
{
orderHeader.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
orderHeader.Lsp_Identification = text.Substring(1, 4);
string orderNumber = text.Substring(5, 20);
orderHeader.Order_Number = orderNumber.Substring(3, 17);
orderHeader.Order_Type = text.Substring(25, 4);
string storeNumber = text.Substring(29, 10);
orderHeader.Store_Number = storeNumber.Substring(5, 5);
string deliveryDate = text.Substring(39, 8);
int year = DAL.IntReturn(deliveryDate.Substring(0, 4));
int month = DAL.IntReturn(deliveryDate.Substring(4, 2));
int day = DAL.IntReturn(deliveryDate.Substring(6, 2));
orderHeader.Plan_Delivery_Date = new DateTime(year, month, day);
string warehouse = text.Substring(47, 20);
orderHeader.Warehouse_Number = warehouse.Substring(15, 5);
string email = text.Substring(67, 20);
orderHeader.MM_Email_Number = email.Substring(15, 5);
string memoField = text.Substring(87, 255);
orderHeader.Memo_Field = memoField.Substring(0, 59);
string dateRecord = text.Substring(342, 8);
int year1 = DAL.IntReturn(dateRecord.Substring(0, 4));
int month1 = DAL.IntReturn(dateRecord.Substring(4, 2));
int day1 = DAL.IntReturn(dateRecord.Substring(6, 2));
orderHeader.Date_Record = new DateTime(year1, month1, day1);
orderHeader.Action_Type = text.Substring(350, 1);
string commercial = text.Substring(351, 10);
orderHeader.Commercial_Supplier_Number = commercial.Substring(4, 5);
string dateToWarehouse = text.Substring(361, 8);
int year2 = DAL.IntReturn(dateToWarehouse.Substring(0, 4));
int month2 = DAL.IntReturn(dateToWarehouse.Substring(4, 2));
int day2 = DAL.IntReturn(dateToWarehouse.Substring(6, 2));
orderHeader.Plan_Delivery_To_Warehouse = new DateTime(year2, month2, day2);
string orderDate = text.Substring(369, 8);
int year3 = DAL.IntReturn(orderDate.Substring(0, 4));
int month3 = DAL.IntReturn(orderDate.Substring(4, 2));
int day3 = DAL.IntReturn(orderDate.Substring(6, 2));
orderHeader.Order_Date = new DateTime(year3, month3, day3);
orderHeader.Free_Text1 = text.Substring(377, 20);
orderHeader.Free_Text2 = text.Substring(397, 20);
orderHeader.Order_Logistic_Type = DAL.IntReturn(text.Substring(417, 2));
orderHeader.Customer_Store_Number = DAL.IntReturn(text.Substring(419, 2));
orderHeader.Customer_Number = text.Substring(421, 6);
orderHeader.FILENAME = fileName;
string sql = string.Format("select * from MM_ORDERS_HEADER where Order_Number = '{0}'", orderHeader.Order_Number);
DataTable orderHeaderTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(orderHeaderTable.Rows.Count);
if (count > 0 && (orderHeader.Action_Type == "I" || orderHeader.Action_Type == "D"))
{
orderHeader.EDITDATE = DateTime.Now;
check = orderHeader.Update(fileName, text);
result.result(check, orderHeader.Action_Type, orderHeader.Order_Number);
}
else if (count == 0 && orderHeader.Action_Type == "I")
{
orderHeader.ADDDATE = DateTime.Now;
orderHeader.EDITDATE = DateTime.Now;
check = orderHeader.Add(fileName, text);
result.result(check, orderHeader.Action_Type, orderHeader.Order_Number);
}
else
{
check = false;
result.result(check, orderHeader.Action_Type, orderHeader.Order_Number);
}
}
catch (Exception e)
{
string category = "orderHeader";
error.HandleError(fileName, text, e.Message.ToString(), category);
check = false;
result.result(check, orderHeader.Action_Type, orderHeader.Order_Number);
}
}
else if (indicator == 2)
{
ClsMM_Orders_Detail orderDetail = new ClsMM_Orders_Detail();
try
{
orderDetail.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
orderDetail.Lsp_Identification = text.Substring(1, 4);
string orderNumber = text.Substring(5, 20);
orderDetail.Order_Number = orderNumber.Substring(3, 17);
string storeNumber = text.Substring(63, 10);
orderDetail.Store_Number = storeNumber.Substring(5, 5);
string sku = text.Substring(91, 10);
orderDetail.Article_Number = sku.Substring(2, 8);
int quantity = DAL.IntReturn(text.Substring(101, 10));
orderDetail.Order_Quantity = DAL.DecimalReturn(DAL.ChangeValue(3, quantity));
string dateRecord = text.Substring(111, 8);
int year = DAL.IntReturn(dateRecord.Substring(0, 4));
int month = DAL.IntReturn(dateRecord.Substring(4, 2));
int day = DAL.IntReturn(dateRecord.Substring(6, 2));
orderDetail.Date_Record = new DateTime(year, month, day);
orderDetail.Action_Code = text.Substring(119, 1);
string warehouse = text.Substring(120, 20);
orderDetail.Warehouse_Number = warehouse.Substring(15, 5);
orderDetail.Orderline_Fretext = text.Substring(140, 15);
string po = text.Substring(155, 20);
orderDetail.Purchase_Order = po.Substring(0, 12);
string email = text.Substring(175, 20);
orderDetail.MM_Mail_Number = email.Substring(0, 5);
string article = text.Substring(195, 10);
orderDetail.Buying_Article_Number = article.Substring(2, 8);
int price = DAL.IntReturn(text.Substring(205, 13));
orderDetail.Article_Selling_Price = DAL.DecimalReturn(DAL.ChangeValue(2, price));
orderDetail.FILENAME = fileName;
string sql = string.Format("select * from MM_ORDERS_DETAIL where Order_Number = '{0}' and Article_Number='{1}'", orderDetail.Order_Number, orderDetail.Article_Number);
DataTable orderHeaderTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(orderHeaderTable.Rows.Count);
if (count > 0 && (orderDetail.Action_Code == "I" || orderDetail.Action_Code == "D"))
{
orderDetail.EDITDATE = DateTime.Now;
check = orderDetail.Update(fileName, text);
result.result(check, orderDetail.Action_Code, orderDetail.Order_Number);
}
else if (count == 0 && orderDetail.Action_Code == "I")
{
orderDetail.ADDDATE = DateTime.Now;
orderDetail.EDITDATE = DateTime.Now;
check = orderDetail.Add(fileName, text);
result.result(check, orderDetail.Action_Code, orderDetail.Order_Number);
}
else
{
check = false;
result.result(check, orderDetail.Action_Code, orderDetail.Order_Number);
}
}
catch (Exception e)
{
string category = "orderDetail";
error.HandleError(fileName, text, e.Message.ToString(), category);
check = false;
result.result(check, orderDetail.Action_Code, orderDetail.Order_Number);
}
}
return(result);
}
public ResultDatabase_Model InsertDatabase(string text, int indicator, string fileName)
{
bool check = false;
ResultDatabase_Model result = new ResultDatabase_Model();
Response_ReadFile error = new Response_ReadFile();
if (indicator == 3)
{
Articles_Model article = new Articles_Model();
try
{
article.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
article.Action_Code = text.Substring(1, 1);
article.Lsp_Identification = text.Substring(2, 4);
string sku = text.Substring(6, 20);
article.Article_Number = sku.Substring(12, 8);
string shipFrom = text.Substring(26, 20);
article.Supplier_Code = shipFrom.Substring(14, 6);
int buyingUnit = DAL.IntReturn(text.Substring(58, 7));
article.Buying_Unit = DAL.DecimalReturn(DAL.ChangeValue(3, buyingUnit));
article.Selling_Unit = DAL.IntReturn(text.Substring(65, 4));
string orderUnit = text.Substring(69, 4);
article.Ordering_Unit = orderUnit.Substring(1, 3);
article.Article_Type = DAL.IntReturn(text.Substring(73, 1));
article.Article_Status = DAL.IntReturn(text.Substring(75, 1));
article.Buyer_User_Id = text.Substring(93, 6);
article.Desc_Article = text.Substring(99, 33).Replace("'", "\"");
article.Packing_Type = text.Substring(132, 2);
article.Article_Related = DAL.IntReturn(text.Substring(134, 20));
article.EAN_Code = text.Substring(154, 14);
string supplierArticle = text.Substring(168, 14);
article.Supplier_Article_Number = supplierArticle.Substring(0, 8);
article.Article_Desc = text.Substring(182, 33);
article.Number_Mu_Carton = DAL.IntReturn(text.Substring(215, 6));
article.Number_Mu_Layer = DAL.IntReturn(text.Substring(221, 6));
article.Number_Mu_Pallet = DAL.IntReturn(text.Substring(227, 6));
int cartonLength = DAL.IntReturn(text.Substring(233, 8));
article.Carton_Length = DAL.DecimalReturn(DAL.ChangeValue(2, cartonLength));
int cartonGrossweight = DAL.IntReturn(text.Substring(241, 9));
article.Carton_GrossWeight = DAL.DecimalReturn(DAL.ChangeValue(2, cartonGrossweight));
int cartonNetweight = DAL.IntReturn(text.Substring(250, 9));
article.Carton_NetWeight = DAL.DecimalReturn(DAL.ChangeValue(2, cartonNetweight));
article.Warehouse_Number = text.Substring(259, 5);
string dateRecord = text.Substring(264, 8);
int year = DAL.IntReturn(dateRecord.Substring(0, 4));
int month = DAL.IntReturn(dateRecord.Substring(4, 2));
int day = DAL.IntReturn(dateRecord.Substring(6, 2));
article.Date_Record = new DateTime(year, month, day);
article.Logistic_Deposit_Type = DAL.IntReturn(text.Substring(272, 1));
article.Logistic_Flow_Indicator = DAL.IntReturn(text.Substring(273, 2));
article.Ordering_Indicator = DAL.IntReturn(text.Substring(275, 1));
article.Link_Logistic_Deposit = DAL.IntReturn(text.Substring(276, 20));
article.Expiry_Days = DAL.IntReturn(text.Substring(296, 3));
article.Expiry_Days_Checking_Indicator = DAL.IntReturn(text.Substring(299, 1));
article.Minimum_Expiry_Days = DAL.IntReturn(text.Substring(300, 5));
article.Pallet_Indicator = DAL.IntReturn(text.Substring(305, 1));
article.Number_Layer_Pallet = DAL.IntReturn(text.Substring(306, 2));
article.Number_Units_Inbox = DAL.IntReturn(text.Substring(308, 5));
int grossVolumSelling = DAL.IntReturn(text.Substring(314, 15));
article.Gross_Volume = DAL.DecimalReturn(DAL.ChangeValue(3, grossVolumSelling));
article.Measure_Unit_GrossVolume = text.Substring(329, 3);
article.Packing_Type = text.Substring(332, 2);
int cartonWidth = DAL.IntReturn(text.Substring(334, 8));
article.Carton_Width = DAL.DecimalReturn(DAL.ChangeValue(2, cartonWidth));
int cartonHeight = DAL.IntReturn(text.Substring(342, 8));
article.Carton_Height = DAL.DecimalReturn(DAL.ChangeValue(2, cartonHeight));
article.UOM_Carton_Size = text.Substring(350, 3);
article.UOM_Carton_Weight = text.Substring(353, 3);
int cartonGrossVolume = DAL.IntReturn(text.Substring(356, 15));
article.Carton_Gross_Volume = DAL.DecimalReturn(DAL.ChangeValue(3, cartonGrossVolume));
article.UOM_Carton_Volume = text.Substring(371, 3);
article.Selling_Unit_Length = DAL.IntReturn(text.Substring(374, 6));
article.Selling_Unit_Width = DAL.IntReturn(text.Substring(380, 6));
article.Selling_Unit_Height = DAL.IntReturn(text.Substring(386, 6));
article.UOM_Selling_Size = text.Substring(392, 3);
int grossweightSelling = DAL.IntReturn(text.Substring(395, 9));
article.Grossweight_Selling = DAL.DecimalReturn(DAL.ChangeValue(2, grossweightSelling));
int netwweightSelling = DAL.IntReturn(text.Substring(404, 9));
article.Netweight_Selling = DAL.DecimalReturn(DAL.ChangeValue(2, netwweightSelling));
article.UOM_Selling_Weight = text.Substring(413, 3);
article.Article_Group_Number = DAL.IntReturn(text.Substring(416, 3));;
article.Artice_Subgroup_Number = DAL.IntReturn(text.Substring(419, 3));
article.Excise_Tax_Indicator = DAL.IntReturn(text.Substring(422, 1));
int exciseTaxAmount = DAL.IntReturn(text.Substring(423, 15));
article.Excise_Tax_Amount = DAL.DecimalReturn(DAL.ChangeValue(3, exciseTaxAmount));
article.High_Value_Indicator = DAL.IntReturn(text.Substring(438, 1));
article.Storage_Temperature_Desc = text.Substring(439, 25);
article.Remark = text.Substring(464, 33);
article.FILENAME = fileName;
string sql = string.Format("select * from MM_Articles where Article_Number='{0}'", article.Article_Number);
DataTable skuTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(skuTable.Rows.Count);
if (count > 0)
{
article.EditDate = DateTime.Now;
check = article.Update(fileName, text);
result.result(check, article.Article_Status.ToString(), article.Article_Number);
}
else if (count == 0)
{
article.EditDate = DateTime.Now;
article.AddDate = DateTime.Now;
check = article.Add(fileName, text);
result.result(check, article.Article_Status.ToString(), article.Article_Number);
}
else
{
check = false;
result.result(check, article.Article_Status.ToString(), article.Article_Number);
}
}
catch (Exception e)
{
string category = "article";
error.HandleError(fileName, text, e.Message.ToString(), category);
check = false;
result.result(check, article.Article_Status.ToString(), article.Article_Number);
}
}
else if (indicator == 4)
{
BarcodeArticles_Model barcodeArticle = new BarcodeArticles_Model();
try
{
barcodeArticle.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
barcodeArticle.Action_Code = text.Substring(1, 1);
barcodeArticle.Lsp_Identification = text.Substring(2, 4);
string sku = text.Substring(6, 20);
barcodeArticle.Article_Number = sku.Substring(12, 8);
string barcode = text.Substring(26, 20);
barcodeArticle.Barcode = barcode.Substring(0, 14);
barcodeArticle.Warehouse_Number = text.Substring(56, 5);
string dateRecord = text.Substring(61, 8);
int year = DAL.IntReturn(dateRecord.Substring(0, 4));
int month = DAL.IntReturn(dateRecord.Substring(4, 2));
int day = DAL.IntReturn(dateRecord.Substring(6, 2));
barcodeArticle.Date_Record = new DateTime(year, month, day);
barcodeArticle.Barcode_Sequence_Number = DAL.IntReturn(text.Substring(69, 3));
barcodeArticle.FILENAME = fileName;
string sql = string.Format("select * from MM_Barcode_Articles where Article_Number='{0}' and Barcode ='{1}'", barcodeArticle.Article_Number, barcodeArticle.Barcode);
DataTable altSkuTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(altSkuTable.Rows.Count);
if (count > 0)
{
barcodeArticle.Editdate = DateTime.Now;
check = barcodeArticle.Update(fileName, text);
result.result(check, "U", barcodeArticle.Article_Number);
}
else
{
barcodeArticle.AddDate = DateTime.Now;
barcodeArticle.Editdate = DateTime.Now;
check = barcodeArticle.Add(fileName, text);
result.result(check, "I", barcodeArticle.Article_Number);
}
}
catch (Exception e)
{
string category = "barcodeArticle";
error.HandleError(fileName, text, e.Message.ToString(), category);
check = false;
result.result(check, "", barcodeArticle.Article_Number);
}
}
return(result);
}
public Boolean Update(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 4),
new SqlParameter("@Order_Number", SqlDbType.Char, 17),
new SqlParameter("@Order_Type", SqlDbType.Char, 4),
new SqlParameter("@Store_Number", SqlDbType.Char, 5),
new SqlParameter("@Plan_Delivery_Date", SqlDbType.Date, 3),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 6),
new SqlParameter("@MM_Email_Number", SqlDbType.Char, 5),
new SqlParameter("@Memo_Field", SqlDbType.Char, 60),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Action_Type", SqlDbType.Char, 1),
new SqlParameter("@Commercial_Supplier_Number", SqlDbType.Char, 6),
new SqlParameter("@Plan_Delivery_To_Warehouse", SqlDbType.Date, 3),
new SqlParameter("@Order_Date", SqlDbType.Date, 3),
new SqlParameter("@Free_Text1", SqlDbType.Char, 20),
new SqlParameter("@Free_Text2", SqlDbType.Char, 20),
new SqlParameter("@Order_Logistic_Type", SqlDbType.Int, 4),
new SqlParameter("@Customer_Store_Number", SqlDbType.Int, 4),
new SqlParameter("@Customer_Number", SqlDbType.Char, 6),
new SqlParameter("@EDITDATE", SqlDbType.DateTime, 8),
// new SqlParameter( "@STATUS", SqlDbType.NChar, 20),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 100),
};
sp[0].Value = "Update";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Lsp_Identification;
sp[4].Value = this.Order_Number;
sp[5].Value = this.Order_Type;
sp[6].Value = this.Store_Number;
sp[7].Value = this.Plan_Delivery_Date;
sp[8].Value = this.Warehouse_Number;
sp[9].Value = this.MM_Email_Number;
sp[10].Value = this.Memo_Field;
sp[11].Value = this.Date_Record;
sp[12].Value = this.Action_Type;
sp[13].Value = this.Commercial_Supplier_Number;
sp[14].Value = this.Plan_Delivery_To_Warehouse;
sp[15].Value = this.Order_Date;
sp[16].Value = this.Free_Text1;
sp[17].Value = this.Free_Text2;
sp[18].Value = this.Order_Logistic_Type;
sp[19].Value = this.Customer_Store_Number;
sp[20].Value = this.Customer_Number;
sp[21].Value = this.EDITDATE;
//sp[22].Value = this.STATUS;
sp[22].Value = this.FILENAME;
DAL.excutequery(PP_MM_Orders_Header, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "Order_Header";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Order_Number, category);
return(false);
}
}
public Boolean Update(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 4),
new SqlParameter("@Order_Number", SqlDbType.Char, 17),
new SqlParameter("@Store_Number", SqlDbType.Char, 5),
new SqlParameter("@Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Order_Quantity", SqlDbType.Decimal, 9),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Action_Code", SqlDbType.Char, 1),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 6),
new SqlParameter("@Orderline_Fretext", SqlDbType.Char, 15),
new SqlParameter("@Purchase_Order", SqlDbType.Char, 12),
new SqlParameter("@MM_Mail_Number", SqlDbType.Char, 5),
new SqlParameter("@Buying_Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Article_Selling_Price", SqlDbType.Decimal, 9),
new SqlParameter("@EDITDATE", SqlDbType.DateTime, 8),
// new SqlParameter( "@STATUS", SqlDbType.NChar, 20),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 100),
};
sp[0].Value = "Update";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Lsp_Identification;
sp[4].Value = this.Order_Number;
sp[5].Value = this.Store_Number;
sp[6].Value = this.Article_Number;
sp[7].Value = this.Order_Quantity;
sp[8].Value = this.Date_Record;
sp[9].Value = this.Action_Code;
sp[10].Value = this.Warehouse_Number;
sp[11].Value = this.Orderline_Fretext;
sp[12].Value = this.Purchase_Order;
sp[13].Value = this.MM_Mail_Number;
sp[14].Value = this.Buying_Article_Number;
sp[15].Value = this.Article_Selling_Price;
sp[16].Value = this.EDITDATE;
//sp[17].Value = this.STATUS;
sp[17].Value = this.FILENAME;
DAL.excutequery(PP_MM_Orders_Detail, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "Order_Detail";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Order_Number, category);
return(false);
}
}
public Boolean Update(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 4),
new SqlParameter("@Order_Number", SqlDbType.Char, 17),
new SqlParameter("@Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Order_Quantity", SqlDbType.Decimal, 9),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Action_Type", SqlDbType.Char, 1),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 6),
new SqlParameter("@Orderline", SqlDbType.Char, 20),
new SqlParameter("@MM_Email_Number", SqlDbType.Char, 5),
new SqlParameter("@EDITDATE", SqlDbType.DateTime, 8),
new SqlParameter("@BLANK1", SqlDbType.Char, 38),
new SqlParameter("@BLANK2", SqlDbType.Char, 8),
new SqlParameter("@BLANK10", SqlDbType.Char, 10),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 200)
};
sp[0].Value = "Update";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Lsp_Identification;
sp[4].Value = this.Order_Number;
sp[5].Value = this.Article_Number;
sp[6].Value = this.Order_Quantity;
sp[7].Value = this.Date_Record;
sp[8].Value = this.Action_Type;
sp[9].Value = this.Warehouse_Number;
sp[10].Value = this.Orderline;
sp[11].Value = this.MM_Email_Number;
sp[12].Value = this.EDITDATE;
sp[13].Value = this.BLANK1;
sp[14].Value = this.BLANK2;
sp[15].Value = this.BLANK10;
sp[16].Value = this.FILENAME;
DAL.excutequery(PP_MM_PO_Detail, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "PO_DETAIL";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Order_Number, category);
return(false);
}
}
public Boolean Update(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Action_Code", SqlDbType.Char, 4),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 1),
new SqlParameter("@Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Supplier_Code", SqlDbType.Char, 6),
new SqlParameter("@Buying_Unit", SqlDbType.Decimal, 13),
new SqlParameter("@Selling_Unit", SqlDbType.Int, 4),
new SqlParameter("@Ordering_Unit", SqlDbType.Char, 3),
new SqlParameter("@Article_Type", SqlDbType.Int, 4),
new SqlParameter("@Article_Status", SqlDbType.Int, 4),
new SqlParameter("@Buyer_User_Id", SqlDbType.Char, 6),
new SqlParameter("@Desc_Article", SqlDbType.Char, 33),
new SqlParameter("@Packing_type_Article", SqlDbType.Char, 2),
new SqlParameter("@Article_Related", SqlDbType.Int, 4),
new SqlParameter("@EAN_Code", SqlDbType.Char, 14),
new SqlParameter("@Supplier_Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Article_Desc", SqlDbType.Char, 33),
new SqlParameter("@Number_Mu_Carton", SqlDbType.Int, 4),
new SqlParameter("@Number_Mu_Layer", SqlDbType.Int, 4),
new SqlParameter("@Number_Mu_Pallet", SqlDbType.Int, 4),
new SqlParameter("@Carton_Length", SqlDbType.Decimal, 13),
new SqlParameter("@Carton_GrossWeight", SqlDbType.Decimal, 13),
new SqlParameter("@Carton_NetWeight", SqlDbType.Decimal, 13),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 5),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Logistic_Deposit_Type", SqlDbType.Int, 4),
new SqlParameter("@Logistic_Flow_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Ordering_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Link_Logistic_Deposit", SqlDbType.Int, 4),
new SqlParameter("@Expiry_Days", SqlDbType.Int, 4),
new SqlParameter("@Expiry_Days_Checking_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Minimum_Expiry_Days", SqlDbType.Int, 4),
new SqlParameter("@Pallet_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Number_Layer_Pallet", SqlDbType.Int, 4),
new SqlParameter("@Number_Units_Inbox", SqlDbType.Int, 4),
new SqlParameter("@Gross_Volume", SqlDbType.Decimal, 13),
new SqlParameter("@Measure_Unit_GrossVolume", SqlDbType.Char, 3),
new SqlParameter("@Packing_Type", SqlDbType.Char, 2),
new SqlParameter("@Carton_Width", SqlDbType.Decimal, 13),
new SqlParameter("@Carton_Height", SqlDbType.Decimal, 13),
new SqlParameter("@UOM_Carton_Size", SqlDbType.Char, 3),
new SqlParameter("@UOM_Carton_Weight", SqlDbType.Char, 3),
new SqlParameter("@Carton_Gross_Volume", SqlDbType.Decimal, 13),
new SqlParameter("@UOM_Carton_Volume", SqlDbType.Char, 3),
new SqlParameter("@Selling_Unit_Length", SqlDbType.Int, 4),
new SqlParameter("@Selling_Unit_Width", SqlDbType.Int, 4),
new SqlParameter("@Selling_Unit_Height", SqlDbType.Int, 4),
new SqlParameter("@UOM_Selling_Size", SqlDbType.Char, 3),
new SqlParameter("@Grossweight_Selling", SqlDbType.Decimal, 13),
new SqlParameter("@Netweight_Selling", SqlDbType.Decimal, 13),
new SqlParameter("@UOM_Selling_Weight", SqlDbType.Char, 3),
new SqlParameter("@Article_Group_Number", SqlDbType.Int, 4),
new SqlParameter("@Artice_Subgroup_Number", SqlDbType.Int, 4),
new SqlParameter("@Excise_Tax_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Excise_Tax_Amount", SqlDbType.Decimal, 13),
new SqlParameter("@High_Value_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Storage_Temperature_Desc", SqlDbType.Char, 25),
new SqlParameter("@Remark", SqlDbType.NVarChar, 466),
new SqlParameter("@EditDate", SqlDbType.DateTime, 8),
new SqlParameter("@BLANK1", SqlDbType.Char, 4),
new SqlParameter("@BLANK2", SqlDbType.Char, 4),
new SqlParameter("@BLANK3", SqlDbType.Char, 4),
new SqlParameter("@BLANK4", SqlDbType.Char, 1),
new SqlParameter("@BLANK5", SqlDbType.Char, 1),
new SqlParameter("@BLANK6", SqlDbType.Char, 8),
new SqlParameter("@BLANK7", SqlDbType.Char, 8),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 200)
};
sp[0].Value = "Update";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Action_Code;
sp[4].Value = this.Lsp_Identification;
sp[5].Value = this.Article_Number;
sp[6].Value = this.Supplier_Code;
sp[7].Value = this.Buying_Unit;
sp[8].Value = this.Selling_Unit;
sp[9].Value = this.Ordering_Unit;
sp[10].Value = this.Article_Type;
sp[11].Value = this.Article_Status;
sp[12].Value = this.Buyer_User_Id;
sp[13].Value = this.Desc_Article;
sp[14].Value = this.Packing_type_Article;
sp[15].Value = this.Article_Related;
sp[16].Value = this.EAN_Code;
sp[17].Value = this.Supplier_Article_Number;
sp[18].Value = this.Article_Desc;
sp[19].Value = this.Number_Mu_Carton;
sp[20].Value = this.Number_Mu_Layer;
sp[21].Value = this.Number_Mu_Pallet;
sp[22].Value = this.Carton_Length;
sp[23].Value = this.Carton_GrossWeight;
sp[24].Value = this.Carton_NetWeight;
sp[25].Value = this.Warehouse_Number;
sp[26].Value = this.Date_Record;
sp[27].Value = this.Logistic_Deposit_Type;
sp[28].Value = this.Logistic_Flow_Indicator;
sp[29].Value = this.Ordering_Indicator;
sp[30].Value = this.Link_Logistic_Deposit;
sp[31].Value = this.Expiry_Days;
sp[32].Value = this.Expiry_Days_Checking_Indicator;
sp[33].Value = this.Minimum_Expiry_Days;
sp[34].Value = this.Pallet_Indicator;
sp[35].Value = this.Number_Layer_Pallet;
sp[36].Value = this.Number_Units_Inbox;
sp[37].Value = this.Gross_Volume;
sp[38].Value = this.Measure_Unit_GrossVolume;
sp[39].Value = this.Packing_Type;
sp[40].Value = this.Carton_Width;
sp[41].Value = this.Carton_Height;
sp[42].Value = this.UOM_Carton_Size;
sp[43].Value = this.UOM_Carton_Weight;
sp[44].Value = this.Carton_Gross_Volume;
sp[45].Value = this.UOM_Carton_Volume;
sp[46].Value = this.Selling_Unit_Length;
sp[47].Value = this.Selling_Unit_Width;
sp[48].Value = this.Selling_Unit_Height;
sp[49].Value = this.UOM_Selling_Size;
sp[50].Value = this.Grossweight_Selling;
sp[51].Value = this.Netweight_Selling;
sp[52].Value = this.UOM_Selling_Weight;
sp[53].Value = this.Article_Group_Number;
sp[54].Value = this.Artice_Subgroup_Number;
sp[55].Value = this.Excise_Tax_Indicator;
sp[56].Value = this.Excise_Tax_Amount;
sp[57].Value = this.High_Value_Indicator;
sp[58].Value = this.Storage_Temperature_Desc;
sp[59].Value = this.Remark;
sp[60].Value = this.EditDate;
sp[61].Value = this.BLANK1;
sp[62].Value = this.BLANK2;
sp[63].Value = this.BLANK3;
sp[64].Value = this.BLANK4;
sp[65].Value = this.BLANK5;
sp[66].Value = this.BLANK6;
sp[67].Value = this.BLANK7;
sp[68].Value = this.FILENAME;
DAL.excutequery(PP_MM_Articles, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "Article";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Article_Number, category);
return(false);
}
}
private void ConnectFTP()
{
txtInform1.AppendText("\r\n Start Connect SFTP .... \r\n");
Response_SFTP result_SFTP = new Response_SFTP();
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = "sftp01.mmvietnam.com",
UserName = "******",
SshHostKeyFingerprint = "ssh-ed25519 255 gbfFmqgr8jbd8lAQOxHTcVQmNCk5OlnD8MXGMerAttg=",
SshPrivateKeyPath = @"E:\Software\MMVN\lspgmdprod.ppk",
};
using (Session session = new Session())
{
try
{
session.Open(sessionOptions);
}
catch (InvalidOperationException e)
{
result_SFTP.Handle_Error_Sftp("Connect", e.Message.ToString());
txtInform1.AppendText("\r\n Connect SFTP Fail .... \r\n");
return;
}
const string remotePath = Constant.PATH_GETFILE_SFTP;
RemoteDirectoryInfo directory = session.ListDirectory(remotePath);
foreach (RemoteFileInfo fileInfo in directory.Files)
{
if (!fileInfo.IsDirectory && fileInfo.Name.EndsWith(".DAT", StringComparison.OrdinalIgnoreCase))
{
string tempPath = Path.GetTempFileName();
var sourcePath = RemotePath.EscapeFileMask(remotePath + "/" + fileInfo.Name);
string targerPath = Constant.PATH_MOVEFILE_SFTP;
string errorPath = Constant.PATH_MOVEFILE_ERROR_SFTP;
string fileName = fileInfo.Name;
string category = fileName.Substring(9, 2).ToUpper();
string backupPath = string.Format(Constant.PATH_BACKUP, fileName);
try
{
session.GetFiles(sourcePath, backupPath).Check();
session.GetFiles(sourcePath, tempPath).Check();
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
}
string[] lines = File.ReadAllLines(tempPath);
int numberRow = lines.Count();
int countRowSuccess = 0;
ResultDatabase_Model result = new ResultDatabase_Model();
if (category == "MD")
{
this.Show_Message_Start(fileName);
foreach (string text in lines)
{
try
{
int indicator = DAL.IntReturn(text.Substring(0, 1));
}
catch (Exception e)
{
errorPath = string.Format(errorPath, fileName);
session.MoveFile(sourcePath, errorPath);
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
result = CountLineSuccess_MasterData(text, countRowSuccess, fileName);
countRowSuccess = result.numberRowSuccess;
}
if (numberRow == countRowSuccess)
{
this.Show_Message_Success(fileName);
targerPath = string.Format(targerPath, fileName);
try
{
session.MoveFile(sourcePath, targerPath);
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
}
else
{
try
{
errorPath = string.Format(errorPath, fileName);
session.MoveFile(sourcePath, errorPath);
this.Show_Message_Error(fileName, countRowSuccess);
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
}
}
else if (category == "PO")
{
this.Show_Message_Start(fileName);
foreach (string text in lines)
{
try
{
int indicator = DAL.IntReturn(text.Substring(0, 1));
}
catch (Exception e)
{
errorPath = string.Format(errorPath, fileName);
session.MoveFile(sourcePath, errorPath);
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
result = CountLineSuccess_PO(text, countRowSuccess, fileName);
countRowSuccess = result.numberRowSuccess;
}
if (numberRow == countRowSuccess)
{
this.Show_Message_Success(fileName);
targerPath = string.Format(targerPath, fileName);
try
{
session.MoveFile(sourcePath, targerPath);
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
}
else
{
try
{
errorPath = string.Format(errorPath, fileName);
session.MoveFile(sourcePath, errorPath);
this.Show_Message_Error(fileName, countRowSuccess);
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
}
}
else if (category == "SO")
{
this.Show_Message_Start(fileName);
foreach (string text in lines)
{
try
{
int indicator = DAL.IntReturn(text.Substring(0, 1));
}
catch (Exception e)
{
errorPath = string.Format(errorPath, fileName);
session.MoveFile(sourcePath, errorPath);
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
result = CountLineSuccess_SO(text, countRowSuccess, fileName);
countRowSuccess = result.numberRowSuccess;
}
if (numberRow == countRowSuccess)
{
this.Show_Message_Success(fileName);
targerPath = string.Format(targerPath, fileName);
try
{
session.MoveFile(sourcePath, targerPath);
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
}
else
{
try
{
errorPath = string.Format(errorPath, fileName);
session.MoveFile(sourcePath, errorPath);
this.Show_Message_Error(fileName, countRowSuccess);
}
catch (Exception e)
{
result_SFTP.Handle_Error_Sftp(fileName, e.Message.ToString());
continue;
}
}
}
}
}
session.Close();
}
}
public ResultDatabase_Model InsertDatabase(string text, int indicator, string fileName)
{
ResultDatabase_Model result = new ResultDatabase_Model();
Response_ReadFile error = new Response_ReadFile();
bool check = false;
if (indicator == 1)
{
MM_PO_Header poHeader = new MM_PO_Header();
try
{
poHeader.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
poHeader.Lsp_Identification = text.Substring(1, 4);
string orderNumber = text.Substring(5, 20);
poHeader.Order_Number = orderNumber.Substring(3, 17);
poHeader.Order_Type = text.Substring(25, 4);
string supplier = text.Substring(29, 10);
poHeader.Supplier_Number = supplier.Substring(4, 6);
string deliveryDate = text.Substring(39, 8);
int year = DAL.IntReturn(deliveryDate.Substring(0, 4));
int month = DAL.IntReturn(deliveryDate.Substring(4, 2));
int day = DAL.IntReturn(deliveryDate.Substring(6, 2));
poHeader.Plan_Delivery_Date = new DateTime(year, month, day);
string warehouse = text.Substring(47, 20);
poHeader.Warehouse_Number = warehouse.Substring(15, 5);
string email = text.Substring(67, 20);
poHeader.MM_Email_Number = email.Substring(0, 5);
poHeader.Memo_Field = text.Substring(87, 255);
string dateRecord = text.Substring(342, 8);
int year1 = DAL.IntReturn(dateRecord.Substring(0, 4));
int month1 = DAL.IntReturn(dateRecord.Substring(4, 2));
int day1 = DAL.IntReturn(dateRecord.Substring(6, 2));
poHeader.Date_Record = new DateTime(year1, month1, day1);
poHeader.Action_Type = text.Substring(350, 1);
string commercialSupplier = text.Substring(351, 10);
poHeader.Commercial_Supplier_Number = commercialSupplier.Substring(3, 6);
string orderDate = text.Substring(361, 8);
int year3 = DAL.IntReturn(orderDate.Substring(0, 4));
int month3 = DAL.IntReturn(orderDate.Substring(4, 2));
int day3 = DAL.IntReturn(orderDate.Substring(6, 2));
poHeader.Order_Date = new DateTime(year3, month3, day3);
poHeader.Free_Text1 = text.Substring(369, 20);
poHeader.Free_Text2 = text.Substring(389, 20);
poHeader.FILENAME = fileName;
string sql = string.Format("select * from MM_PO_HEADER where Order_Number = '{0}'", poHeader.Order_Number);
DataTable dt = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(dt.Rows.Count);
if (count == 0)
{
poHeader.ADDDATE = DateTime.Now;
poHeader.EDITDATE = DateTime.Now;
check = poHeader.Add(fileName, text);
result.result(check, poHeader.Action_Type, poHeader.Order_Number);
}
else
{
poHeader.EDITDATE = DateTime.Now;
check = poHeader.Update(fileName, text);
result.result(check, poHeader.Action_Type, poHeader.Order_Number);
}
}
catch (Exception e)
{
string Category = "poHeader";
error.HandleError(fileName, text, e.Message.ToString(), Category);
check = false;
result.result(check, poHeader.Action_Type, poHeader.Order_Number);
}
}
else if (indicator == 2)
{
MM_PO_Detail poDetail = new MM_PO_Detail();
try
{
poDetail.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
poDetail.Lsp_Identification = text.Substring(1, 4);
string orderNumber = text.Substring(5, 20);
poDetail.Order_Number = orderNumber.Substring(3, 17);
string sku = text.Substring(81, 10);
poDetail.Article_Number = sku.Substring(2, 8);
int qty = DAL.IntReturn(text.Substring(91, 10));
poDetail.Order_Quantity = DAL.DecimalReturn(DAL.ChangeValue(3, qty));
string dateRecord = text.Substring(101, 8);
int year = DAL.IntReturn(dateRecord.Substring(0, 4));
int month = DAL.IntReturn(dateRecord.Substring(4, 2));
int day = DAL.IntReturn(dateRecord.Substring(6, 2));
poDetail.Date_Record = new DateTime(year, month, day);
poDetail.Action_Type = text.Substring(109, 1);
string warehouse = text.Substring(110, 20);
poDetail.Warehouse_Number = warehouse.Substring(15, 5);
poDetail.Orderline = text.Substring(130, 20);
string email = text.Substring(150, 20);
poDetail.MM_Email_Number = email.Substring(0, 5);
poDetail.FILENAME = fileName;
string sql = string.Format("select * from MM_PO_DETAIL where Order_Number = '{0}' and Article_Number= '{1}'", poDetail.Order_Number, poDetail.Article_Number);
DataTable supplierHeaderTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(supplierHeaderTable.Rows.Count);
if (count == 0)
{
poDetail.ADDDATE = DateTime.Now;
poDetail.EDITDATE = DateTime.Now;
check = poDetail.Add(fileName, text);
result.result(check, poDetail.Action_Type, poDetail.Order_Number);
}
else
{
poDetail.EDITDATE = DateTime.Now;
check = poDetail.Update(fileName, text);
result.result(check, poDetail.Action_Type, poDetail.Order_Number);
}
}
catch (Exception e)
{
string Category = "poDetail";
error.HandleError(fileName, text, e.Message.ToString(), Category);
check = false;
result.result(check, poDetail.Action_Type, poDetail.Order_Number);
}
}
else if (indicator == 3)
{
MM_PO_Detail_XD poDetailXd = new MM_PO_Detail_XD();
try
{
poDetailXd.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
poDetailXd.Lsp_Identification = text.Substring(1, 4);
string orderNumber = text.Substring(5, 20);
poDetailXd.Order_Number = orderNumber.Substring(3, 17);
string sku = text.Substring(63, 10);
poDetailXd.Article_Number = sku.Substring(2, 8);
string storeNumber = text.Substring(73, 10);
poDetailXd.Store_Number = storeNumber.Substring(8, 2);
int qty = DAL.IntReturn(text.Substring(83, 10));
poDetailXd.Order_Quantity = DAL.DecimalReturn(DAL.ChangeValue(3, qty));
string warehouse = text.Substring(93, 20);
poDetailXd.Warehouse_Number = warehouse.Substring(15, 5);
string saleOrder = text.Substring(121, 20);
poDetailXd.Sale_Order = saleOrder.Substring(3, 17);
string mail = text.Substring(141, 20);
poDetailXd.Metro_Mail = mail.Substring(0, 5);
string articleNumber = text.Substring(161, 10);
poDetailXd.Buying_Article_Number = articleNumber.Substring(0, 7);
poDetailXd.FILENAME = fileName;
string sql = string.Format("select * from MM_PO_DETAIL_XD where Order_Number = '{0}' and Sale_Order='{1}' and Article_Number='{2}'", poDetailXd.Order_Number, poDetailXd.Sale_Order, poDetailXd.Article_Number);
DataTable supplierHeaderTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(supplierHeaderTable.Rows.Count);
if (count == 0)
{
poDetailXd.ADDDATE = DateTime.Now;
poDetailXd.EDITDATE = DateTime.Now;
check = poDetailXd.Add(fileName, text);
result.result(check, "", poDetailXd.Order_Number);
}
else
{
poDetailXd.EDITDATE = DateTime.Now;
check = poDetailXd.Update(fileName, text);
result.result(check, "", poDetailXd.Order_Number);
}
}
catch (Exception e)
{
string Category = "poDetailXd";
error.HandleError(fileName, text, e.Message.ToString(), Category);
check = false;
result.result(check, "", poDetailXd.Order_Number);
}
}
return(result);
}
public ResultDatabase_Model InsertDatabase(string text, int indicator, string fileName)
{
ResultDatabase_Model result = new ResultDatabase_Model();
Response_ReadFile error = new Response_ReadFile();
bool check = false;
if (indicator == 1)
{
Supplier_Header_Model supplierHeader = new Supplier_Header_Model();
try
{
supplierHeader.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
supplierHeader.Action_Code = text.Substring(1, 1);
supplierHeader.Lsp_Identification = text.Substring(2, 4).ToString();
string supplierCode = text.Substring(6, 20);
supplierHeader.Supplier_Code = supplierCode.Substring(14, 6).ToString();
supplierHeader.Supplier_Name = text.Substring(26, 35).Replace("'", "\"");
supplierHeader.Supplier_Status = DAL.IntReturn(text.Substring(61, 2));
supplierHeader.Supplier_Type = DAL.IntReturn(text.Substring(63, 2));
supplierHeader.Buyer_User_Id = text.Substring(65, 6);
supplierHeader.Supplier_Fiscal_Number = text.Substring(71, 15);
supplierHeader.Way_Of_Order = DAL.IntReturn(text.Substring(86, 1));
supplierHeader.Language_Code = text.Substring(87, 2);
supplierHeader.Currency_Code = text.Substring(89, 3);
//string warehouse= text.Substring(92, 5);
supplierHeader.Warehouse_Number = text.Substring(92, 5);
string dateRecord = text.Substring(97, 8);
int year = DAL.IntReturn(dateRecord.Substring(0, 4));
int month = DAL.IntReturn(dateRecord.Substring(4, 2));
int day = DAL.IntReturn(dateRecord.Substring(6, 2));
supplierHeader.Date_Record = new DateTime(year, month, day);
supplierHeader.Default_Logistic_Flow = int.Parse(text.Substring(105, 2));
supplierHeader.Supplier_Registration_Number = text.Substring(107, 15);
supplierHeader.FILENAME = fileName;
string sql = string.Format("select * from MM_Supplier_Header where Supplier_Code = '{0}' and Warehouse_Number='{1}' ", supplierHeader.Supplier_Code, supplierHeader.Warehouse_Number);
DataTable supplierHeaderTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(supplierHeaderTable.Rows.Count);
if (count > 0 && supplierHeader.Supplier_Status == 1)
{
supplierHeader.EditDate = DateTime.Now;
check = supplierHeader.Update(fileName, text);
result.result(check, supplierHeader.Supplier_Status.ToString(), supplierHeader.Supplier_Code);
}
else if (count > 0 && (supplierHeader.Supplier_Status == 5 || supplierHeader.Supplier_Status == 9))
{
supplierHeader.EditDate = DateTime.Now;
check = supplierHeader.Update(fileName, text);
result.result(check, supplierHeader.Supplier_Status.ToString(), supplierHeader.Supplier_Code);
}
else if (count == 0 && supplierHeader.Supplier_Status == 1)
{
supplierHeader.AddDate = DateTime.Now;
supplierHeader.EditDate = DateTime.Now;
check = supplierHeader.Add(fileName, text);
result.result(check, supplierHeader.Supplier_Status.ToString(), supplierHeader.Supplier_Code);
}
else
{
check = false;
result.result(check, supplierHeader.Supplier_Status.ToString(), supplierHeader.Supplier_Code);
}
}
catch (Exception e)
{
string category = "supplierHeader";
error.HandleError(fileName, text, e.Message.ToString(), category);
check = false;
result.result(check, supplierHeader.Supplier_Status.ToString(), supplierHeader.Supplier_Code);
}
}
else if (indicator == 2)
{
Supplier_Detail_Model supplierDetail = new Supplier_Detail_Model();
try
{
supplierDetail.Table_Indicator = DAL.IntReturn(text.Substring(0, 1));
supplierDetail.Action_Code = text.Substring(1, 1);
supplierDetail.Lsp_Identification = text.Substring(2, 4);
string supplierCode = text.Substring(6, 20);
supplierDetail.Supplier_Code = supplierCode.Substring(14, 6).ToString();
supplierDetail.Address_Indicator = DAL.IntReturn(text.Substring(26, 2));
supplierDetail.Contact_Person = text.Substring(28, 35);
supplierDetail.Building = text.Substring(63, 28);
supplierDetail.Town = text.Substring(119, 25);
supplierDetail.Phone = text.Substring(144, 15);
supplierDetail.Fax_Number = text.Substring(159, 18);
supplierDetail.Postal_Code = text.Substring(176, 11);
supplierDetail.DC_Phone_Number = text.Substring(188, 15);
supplierDetail.Origin = text.Substring(221, 4);
supplierDetail.Supplier_Repressentative = text.Substring(225, 35);
supplierDetail.Warehouse_Number = text.Substring(260, 5).ToString();
string dateRecord = text.Substring(265, 8);
int year = DAL.IntReturn(dateRecord.Substring(0, 4));
int month = DAL.IntReturn(dateRecord.Substring(4, 2));
int day = DAL.IntReturn(dateRecord.Substring(6, 2));
supplierDetail.Date_Record = new DateTime(year, month, day);
supplierDetail.Email_Address = text.Substring(273, 100);
supplierDetail.FILENAME = fileName;
string sql = string.Format("select * from MM_Supplier_Detail where Supplier_Code = '{0}' and Warehouse_Number='{1}' ", supplierDetail.Supplier_Code, supplierDetail.Warehouse_Number);
DataTable supplierDetailTable = DAL.SELECT_SQL(sql);
int count = DAL.IntReturn(supplierDetailTable.Rows.Count);
if (count > 0)
{
supplierDetail.Editdate = DateTime.Now;
check = supplierDetail.Update(fileName, text);
result.result(check, "U", supplierDetail.Supplier_Code);
}
else if (count == 0)
{
supplierDetail.AddDate = DateTime.Now;
supplierDetail.Editdate = DateTime.Now;
check = supplierDetail.Add(fileName, text);
result.result(check, "I", supplierDetail.Supplier_Code);
}
}
catch (Exception e)
{
string category = "supplierDetail";
error.HandleError(fileName, text, e.Message.ToString(), category);
check = false;
result.result(check, "", supplierDetail.Supplier_Code);
}
}
return(result);
}
public Boolean Update(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Action_Code", SqlDbType.Char, 1),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 4),
new SqlParameter("@Supplier_Code", SqlDbType.Char, 6),
new SqlParameter("@Supplier_Name", SqlDbType.Char, 35),
new SqlParameter("@Supplier_Status", SqlDbType.Int, 4),
new SqlParameter("@Supplier_Type", SqlDbType.Int, 4),
new SqlParameter("@Buyer_User_Id", SqlDbType.Char, 6),
new SqlParameter("@Supplier_Fiscal_Number", SqlDbType.Char, 15),
new SqlParameter("@Way_Of_Order", SqlDbType.Int, 4),
new SqlParameter("@Language_Code", SqlDbType.Char, 2),
new SqlParameter("@Currency_Code", SqlDbType.Char, 3),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 5),
new SqlParameter("@Date_Record", SqlDbType.Date, 3),
new SqlParameter("@Default_Logistic_Flow", SqlDbType.Int, 4),
new SqlParameter("@Supplier_Registration_Number", SqlDbType.Char, 15),
new SqlParameter("@EditDate", SqlDbType.DateTime, 8),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 200),
};
sp[0].Value = "Update";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Action_Code;
sp[4].Value = this.Lsp_Identification;
sp[5].Value = this.Supplier_Code;
sp[6].Value = this.Supplier_Name;
sp[7].Value = this.Supplier_Status;
sp[8].Value = this.Supplier_Type;
sp[9].Value = this.Buyer_User_Id;
sp[10].Value = this.Supplier_Fiscal_Number;
sp[11].Value = this.Way_Of_Order;
sp[12].Value = this.Language_Code;
sp[13].Value = this.Currency_Code;
sp[14].Value = this.Warehouse_Number;
sp[15].Value = this.Date_Record;
sp[16].Value = this.Default_Logistic_Flow;
sp[17].Value = this.Supplier_Registration_Number;
sp[18].Value = this.EditDate;
sp[19].Value = this.FILENAME;
DAL.excutequery(PP_MM_Supplier_Header, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "SUPPLIER_HEADER";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Supplier_Code, category);
return(false);
}
}
public Boolean Add(string fileName, string text)
{
try
{
SqlParameter[] sp =
{
new SqlParameter("@Action", SqlDbType.NVarChar, 255),
new SqlParameter("@Serialkey", SqlDbType.Int, 4),
new SqlParameter("@Table_Indicator", SqlDbType.Int, 4),
new SqlParameter("@Lsp_Identification", SqlDbType.Char, 4),
new SqlParameter("@Order_Number", SqlDbType.Char, 17),
new SqlParameter("@Article_Number", SqlDbType.Char, 8),
new SqlParameter("@Store_Number", SqlDbType.Char, 5),
new SqlParameter("@Order_Quantity", SqlDbType.Decimal, 9),
new SqlParameter("@Warehouse_Number", SqlDbType.Char, 6),
new SqlParameter("@Sale_Order", SqlDbType.Char, 17),
new SqlParameter("@Metro_Mail", SqlDbType.Char, 5),
new SqlParameter("@Buying_Article_Number", SqlDbType.Char, 7),
new SqlParameter("@ADDDATE", SqlDbType.DateTime, 8),
new SqlParameter("@EDITDATE", SqlDbType.DateTime, 8),
new SqlParameter("@BLANK1", SqlDbType.Char, 38),
new SqlParameter("@BLANK2", SqlDbType.Char, 8),
new SqlParameter("@FILENAME", SqlDbType.NVarChar, 200),
new SqlParameter("@STATUS", SqlDbType.NVarChar, 40),
};
sp[0].Value = "Addnew";
sp[1].Value = this.Serialkey;
sp[2].Value = this.Table_Indicator;
sp[3].Value = this.Lsp_Identification;
sp[4].Value = this.Order_Number;
sp[5].Value = this.Article_Number;
sp[6].Value = this.Store_Number;
sp[7].Value = this.Order_Quantity;
sp[8].Value = this.Warehouse_Number;
sp[9].Value = this.Sale_Order;
sp[10].Value = this.Metro_Mail;
sp[11].Value = this.Buying_Article_Number;
sp[12].Value = this.ADDDATE;
sp[13].Value = this.EDITDATE;
sp[14].Value = this.BLANK1;
sp[15].Value = this.BLANK2;
sp[16].Value = this.FILENAME;
sp[17].Value = this.STATUS;
DAL.excutequery(PP_MM_PO_Detail_XD, sp, CommandType.StoredProcedure);
return(true);
}
catch (Exception e)
{
string category = "PO_DETAIL_XDOCK";
ResultDatabase_Model result = new ResultDatabase_Model();
result.HandleError(fileName, text, e.Message.ToString(), this.Order_Number, category);
return(false);
}
}