public static void Main(String[] arg) { UpdateVatForCategory12(); return; TestProject.Master.InvoiceProductPatch ABCD = new Master.InvoiceProductPatch(); ABCD.UpdateTaxRate(); return; InvoicePatch obj = new InvoicePatch(); obj.UpdateInvoiceAmount(); return; string TaxGroup1 = "CST*9"; string TaxType = string.Empty; decimal TaxRate = 0.00M; if (!string.IsNullOrEmpty(TaxGroup1)) { string[] splitTax = TaxGroup1.Split('-'); if (splitTax.Count() > 1) { TaxType = splitTax[0]; TaxRate = Converter.ToDecimal(splitTax[1]); } else { TaxType = TaxGroup1; TaxRate = 0.00M; } } decimal VatCstRate = 0M; if (TaxType == "CST") { if (TaxRate > 0.00M) { VatCstRate = TaxRate + 100; } } else { if (TaxRate > 0.00M) { VatCstRate = TaxRate + 100; } } // //InvoiceServiceTest(); // //Note: DateTime object // //.ToString("dd.MM.yyyy", CultureInfo.InvariantCulture); // //Step-1 // //CreditNotePatch crPatch = new CreditNotePatch(); // //crPatch.UpdateCrNote(); // //crPatch.DeleteDuplicateCrNote(); // //Step-2 // //InvoicePatch obj = new InvoicePatch(); // //obj.UpdateInvoice(); // //Step-3 // //DuplicateInvoiceProductPatch obj1 = new DuplicateInvoiceProductPatch(); // //obj1.UpdateInvoiceProductMasterStatus(); return; // //IDemo a = new Demo(); // //a.Print(); // //Abc ob = new Abc(); // //ob.A(); // //return; // //Console.WriteLine("A" + "\r\n" + "A"); // //Console.WriteLine(DecimalToWords(325.46M)); int UserID = 3; string FilePath = @"D:\DONT DELETE\Desktop\Invoietoday\invoice (188).xlsx";//Invoicing feed.xlsx"; int SheetType = 1; string FileName = "Invoicing feed"; string con = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FilePath + ";Extended Properties ='Excel 8.0;HDR=Yes';"; using (OleDbConnection OleDbCon = new OleDbConnection(con)) { OleDbCon.Open(); OleDbCommand oleDbCommand = new OleDbCommand(); if (SheetType == 1) { oleDbCommand = new OleDbCommand("Select * From [Invoicing$]", OleDbCon); //oleDbCommand = new OleDbCommand("Select [Date], [Invoice No],[Party's Name], [Order ID], [Order Date], [Address], [Email ID], [Mobile No], [Product], [Rate], [Quantity], [Total], [Net Sales], [VAT], [Packaging], [Invoice Amt], [Sales Group] From [Invoicing feed$] Where [Invoice No] > 0", OleDbCon); } else if (SheetType == 2) { oleDbCommand = new OleDbCommand("Select SettlementDate,Order ID,Invoice No,Invoice Date,Product Code,Quantity,Invoice Amt,Settlement Amount,Status from [Amount Receivable$]", OleDbCon); } else if (SheetType == 3) { //oleDbCommand = new OleDbCommand("Select Product Code, Title, Size, MRP, PosterType, PosterDimensions,Dimensions in Inches, Poster Weight, Package Contents, Packaging Information, Shipping Duration, Variant, Color, Keywords," + // "SocialName,Publisher,PaperType,Description,Category,SubCategory,ArtistName,PaintingStyle,BlackAndWhitePoster,ColorPoster,PaperFinish,Shape,Orientation,Framed,FrameMaterial,OtherFrameDetails,Width,Height,PaperDepth," + // "Weight,OtherDimensions,OtherFeatures,SupplierImage,ImageLink,Note,WarrantSummary From MasterSheet"); oleDbCommand = new OleDbCommand("Select * From [Master Sheet$]", OleDbCon); } OleDbDataReader dr = oleDbCommand.ExecuteReader(); StreamWriter sw = new StreamWriter(FileName); sw.WriteLine(" InvoiceNo InvoiceDate OrderID ProductID ErrorMessage"); while (dr.Read()) { int invoiceNo = 0; string orderID = string.Empty; DateTime invoiceDate = new DateTime(); string productID = ""; string ErrorMsg = string.Empty; int Result = 0; try { Dictionary <int, SqlParameter> sqlParam = new Dictionary <int, SqlParameter>(); if (SheetType == 1) { int quantity = 0; decimal rate, unitPrice, netSales, total = 0.00M; decimal CST = 0.00M, VAT = 0.00M, ShippingHandling = 0.00M; DateTime orderDate = new DateTime(); string TaxGroup, PartyName, Address, EmailID, MobNo, Vendor; orderID = Converter.ToString(dr["Order ID"].ToString()); if (string.IsNullOrEmpty(orderID)) { continue; } orderDate = Converter.ToDateTime(dr["Date"]); if (orderDate <= new DateTime(1900, 01, 01)) { orderDate = DateTime.Now.Date; } invoiceNo = 0; // //Converter.ToInt(dr["Invoice No"]); // //if (invoiceNo == 0) continue; invoiceDate = DateTime.Now.Date; productID = Converter.ToString(dr["SKU"]).ToUpper(); quantity = Converter.ToInt(dr["Quantity"]); rate = Converter.ToDecimal(dr["Rate"]); TaxGroup = Converter.ToString(dr["Tax Group"]); PartyName = Converter.ToString(dr["Party's Name"]); Address = Converter.ToString(dr["Address"]); EmailID = Converter.ToString(dr["Email ID"]); MobNo = Converter.ToString(dr["Mobile No"]); ShippingHandling = Math.Round((Converter.ToDecimal(dr["Shipping & Handling"])), 2); Vendor = Converter.ToString(dr["Sales Group"]); if (TaxGroup == "CST") { unitPrice = Math.Round(((rate / 102) * 100), 2); CST = (rate - unitPrice) * quantity; } else { unitPrice = Math.Round(((rate / 105) * 100), 2); VAT = (rate - unitPrice) * quantity; } netSales = unitPrice * quantity; total = (rate * quantity) + Math.Round((Converter.ToDecimal(dr["Shipping & Handling"])), 2); sqlParam[0] = new SqlParameter("@datetimeInvoiceDate", invoiceDate); sqlParam[1] = new SqlParameter("@intInvoicNo", invoiceNo); sqlParam[2] = new SqlParameter("@varcharPartysName", dr[2]); sqlParam[3] = new SqlParameter("@intOrderID", orderID); sqlParam[4] = new SqlParameter("@datetimeOrderDate", Convert.ToDateTime(dr[4])); sqlParam[5] = new SqlParameter("@varcharAddress", dr[5]); sqlParam[6] = new SqlParameter("@varcharEmailID", dr[6]); sqlParam[7] = new SqlParameter("@varcharMobileNo", dr[7]); sqlParam[8] = new SqlParameter("@varcharProductID", productID); sqlParam[9] = new SqlParameter("@decimalRate", Convert.ToDecimal(dr[9])); sqlParam[10] = new SqlParameter("@intQuantity", Convert.ToInt32(dr[10])); sqlParam[11] = new SqlParameter("@decimalTotal", Convert.ToDecimal(dr[11])); sqlParam[12] = new SqlParameter("@decimalNetSale", Convert.ToDecimal(dr[12])); sqlParam[13] = new SqlParameter("@decimalVat", Convert.ToDecimal(dr[13])); sqlParam[14] = new SqlParameter("@decimalPackagingAndForwarding", Convert.ToDecimal(dr[14])); sqlParam[15] = new SqlParameter("@decimalInvoiceAmount", Convert.ToDecimal(dr[15])); sqlParam[16] = new SqlParameter("@nvarcharVendor", dr[16]); sqlParam[17] = new SqlParameter("@intCreatedModifiedBy", UserID); if (SqlUtility.ExecuteCommandSpReturnVal(GetConnectionString(), "sp_im_InsertInvoicingFeed", sqlParam) > 0) { return; } } if (SheetType == 2) { invoiceNo = Convert.ToInt32(dr[2]); orderID = Convert.ToString(dr[1]); invoiceDate = Convert.ToDateTime(dr[3]); productID = dr[4].ToString(); sqlParam[0] = new SqlParameter("@datetimeSettlementDate", dr[0]); sqlParam[1] = new SqlParameter("@intOrderID", dr[1]); sqlParam[2] = new SqlParameter("@intInvoiceNo", dr[2]); sqlParam[3] = new SqlParameter("@datetimeInvoiceDate", dr[3]); sqlParam[4] = new SqlParameter("@varcharProductID", dr[4]); sqlParam[5] = new SqlParameter("@decimalSettlementAmount", dr[5]); sqlParam[6] = new SqlParameter("@intStatus", dr[6]); sqlParam[7] = new SqlParameter("@intCreatedModifiedBy", UserID); if (SqlUtility.ExecuteCommandSpReturnVal(GetConnectionString(), "sp_im_UpdateAmountReceivable", sqlParam) > 0) { return; } } if (SheetType == 3) { productID = dr[0].ToString(); sqlParam[0] = new SqlParameter("@varcharProductID", dr[0]); sqlParam[1] = new SqlParameter("@varcharTitle", dr[1]); sqlParam[2] = new SqlParameter("@varcharSize", dr[2]); sqlParam[3] = new SqlParameter("@decimalMRP", dr[3]); sqlParam[4] = new SqlParameter("@varcharPosterType", dr[4]); sqlParam[5] = new SqlParameter("@varcharPosterDimensions", dr[5]); sqlParam[6] = new SqlParameter("@varcharDimensionsInInches", dr[6]); sqlParam[7] = new SqlParameter("@intPosterWeight", dr[7]); sqlParam[8] = new SqlParameter("@varcharPackageContents", dr[8]); sqlParam[9] = new SqlParameter("@varcharPosterDimensions", dr[9]); sqlParam[10] = new SqlParameter("@varcharPackagingInformation", dr[10]); sqlParam[11] = new SqlParameter("@intShippingDuration", dr[11]); sqlParam[12] = new SqlParameter("@varcharVariant", dr[12]); sqlParam[13] = new SqlParameter("@varcharColor", dr[13]); sqlParam[14] = new SqlParameter("@varcharKeywords", dr[14]); sqlParam[15] = new SqlParameter("@varcharSocialName", dr[15]); sqlParam[16] = new SqlParameter("@varcharPublisher", dr[16]); sqlParam[17] = new SqlParameter("@varcharPaperType", dr[17]); sqlParam[18] = new SqlParameter("@varcharDescription", dr[18]); sqlParam[19] = new SqlParameter("@varcharCategory", dr[19]); sqlParam[20] = new SqlParameter("@varcharSubCategory", dr[20]); sqlParam[21] = new SqlParameter("@varcharArtistName", dr[21]); sqlParam[22] = new SqlParameter("@varcharPaintingStyle", dr[22]); sqlParam[23] = new SqlParameter("@varcharBlackAndWhitePoster", dr[23]); sqlParam[24] = new SqlParameter("@varcharColorPoster", dr[24]); sqlParam[25] = new SqlParameter("@varcharPaperFinish", dr[25]); sqlParam[26] = new SqlParameter("@varcharShape", dr[26]); sqlParam[27] = new SqlParameter("@varcharOrientation", dr[27]); sqlParam[28] = new SqlParameter("@varcharFramed", dr[28]); sqlParam[29] = new SqlParameter("@varcharFrameMaterial", dr[29]); sqlParam[30] = new SqlParameter("@varcharOtherFrameDetails", dr[30]); sqlParam[31] = new SqlParameter("@intwidth", dr[31]); sqlParam[32] = new SqlParameter("@intHeight", dr[32]); sqlParam[33] = new SqlParameter("@intPaperDepth", dr[33]); sqlParam[34] = new SqlParameter("@decimalWeight", dr[34]); sqlParam[35] = new SqlParameter("@varcharOtherDimensions", dr[35]); sqlParam[36] = new SqlParameter("@varcharOtherFeatures", dr[36]); sqlParam[37] = new SqlParameter("@varcharSupplierImage", dr[37]); sqlParam[38] = new SqlParameter("@varcharImageLink", dr[38]); sqlParam[39] = new SqlParameter("@varcharNote", dr[39]); sqlParam[40] = new SqlParameter("@varcharWarrantySummary", dr[40]); sqlParam[41] = new SqlParameter("@intCreatedModifiedBy", UserID); if (SqlUtility.ExecuteCommandSpReturnVal(GetConnectionString(), "sp_pm_InsertProduct", sqlParam) > 0) { return; } } return; } catch (Exception ex) { sw.WriteLine(invoiceNo + " " + orderID + " " + invoiceDate.ToString("dd-MM-yyyy") + " " + productID + " " + ex.Message); } } //string val = IMSLibrary.EncryptDecrypt.Encrypt("1234", "Rohan"); //System.Threading.Thread t = new System.Threading.Thread(Run); //Console.WriteLine("Line 1"); //t.Start(); //Console.WriteLine("Line 2"); //t.Join(); //Console.WriteLine("Line 3"); //Console.ReadLine(); } }