public static List <Invoiceitems> FK_withoutJoin(string id, DAL DAL) { string q = " SELECT * FROM " + INVOICEITEMS.INVOICEITEMS_TBL + " \r\n"; q += " WHERE " + INVOICEITEMS.INVOICEITEMS_TBL + "." + INVOICE.INVOICE_ID + " = '" + id + "' \r\n"; q += " ORDER BY " + INVOICEITEMS.INVOICEITEMS_TBL + "." + INVOICEITEMS.INVOICEITEMS_ID + ";\r\n"; List <Invoiceitems> list = new List <Invoiceitems>(); using (IDataReader redr = new DAL().Listreader(q)) { while (redr.Read() == true) { Invoiceitems obj = new Invoiceitems() { Invoiceitems_id = redr[INVOICEITEMS.INVOICEITEMS_ID].ToString(), Invoice_id = redr[INVOICEITEMS.INVOICE_ID].ToString(), Po_no = redr[INVOICEITEMS.PO_NO].ToString(), Dc_no = redr[INVOICEITEMS.DC_NO].ToString(), Product_id = redr[INVOICEITEMS.PRODUCT_ID].ToString(), Sizes_id = redr[INVOICEITEMS.SIZES_ID].ToString(), Qty = redr[INVOICEITEMS.QTY].ToString(), Price = ConvertTO.Decimal(redr[INVOICEITEMS.PRICE].ToString()), Cgst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.CGST_PERCENT].ToString()), Sgst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.SGST_PERCENT].ToString()), Igst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.IGST_PERCENT].ToString()), Refered_id = redr[INVOICEITEMS.REFERED_ID].ToString(), Locked = redr[INVOICEITEMS.LOCKED].ToString(), }; list.Add(obj); } } return(list); }
private static List <Invoiceitems> EntityList(string q) { List <Invoiceitems> list = new List <Invoiceitems>(); using (IDataReader redr = new DAL().Listreader(q)) { while (redr.Read() == true) { Invoiceitems obj = new Invoiceitems() { Invoiceitems_id = redr[INVOICEITEMS.INVOICEITEMS_ID].ToString(), Invoice_id = redr[INVOICEITEMS.INVOICE_ID].ToString(), Po_no = redr[INVOICEITEMS.PO_NO].ToString(), Dc_no = redr[INVOICEITEMS.DC_NO].ToString(), Product_id = redr[PRODUCT.PRODUCT_NAME].ToString(), Sizes_id = redr[SIZES.SIZES_NAME].ToString(), Colours_id = redr[COLOURS.COLOURS_NAME].ToString(), Qty = redr[INVOICEITEMS.QTY].ToString(), Price = ConvertTO.Decimal(redr[INVOICEITEMS.PRICE].ToString()), Cgst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.CGST_PERCENT].ToString()), Sgst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.SGST_PERCENT].ToString()), Igst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.IGST_PERCENT].ToString()), Refered_id = redr[INVOICEITEMS.REFERED_ID].ToString(), Locked = redr[INVOICEITEMS.LOCKED].ToString(), }; list.Add(obj); } } return(list); }
public static void InsertSub(Invoiceitems v, DAL dalsession) { string q = " INSERT INTO " + INVOICEITEMS.INVOICEITEMS_TBL + " ( "; q += " " + INVOICEITEMS.INVOICE_ID + " \r\n "; q += "," + INVOICEITEMS.PO_NO + " \r\n "; q += "," + INVOICEITEMS.DC_NO + " \r\n "; q += "," + INVOICEITEMS.PRODUCT_ID + " \r\n "; q += "," + INVOICEITEMS.SIZES_ID + " \r\n "; q += "," + INVOICEITEMS.COLOURS_ID + " \r\n "; q += "," + INVOICEITEMS.QTY + " \r\n "; q += "," + INVOICEITEMS.PRICE + " \r\n "; q += "," + INVOICEITEMS.CGST_PERCENT + " \r\n "; q += "," + INVOICEITEMS.SGST_PERCENT + " \r\n "; q += "," + INVOICEITEMS.IGST_PERCENT + " \r\n "; q += "," + INVOICEITEMS.REFERED_ID + " \r\n "; q += "," + INVOICEITEMS.LOCKED + " \r\n "; q += " ) VALUES ( "; q += " @" + INVOICEITEMS.INVOICE_ID + "@ \r\n "; q += ", @" + INVOICEITEMS.PO_NO + "@ \r\n "; q += ", @" + INVOICEITEMS.DC_NO + "@ \r\n "; q += ", @" + INVOICEITEMS.PRODUCT_ID + "@ \r\n "; q += ", @" + INVOICEITEMS.SIZES_ID + "@ \r\n "; q += ", @" + INVOICEITEMS.COLOURS_ID + "@ \r\n "; q += ", @" + INVOICEITEMS.QTY + "@ \r\n "; q += ", @" + INVOICEITEMS.PRICE + "@ \r\n "; q += ", @" + INVOICEITEMS.CGST_PERCENT + "@ \r\n "; q += ", @" + INVOICEITEMS.SGST_PERCENT + "@ \r\n "; q += ", @" + INVOICEITEMS.IGST_PERCENT + "@ \r\n "; q += ", @" + INVOICEITEMS.REFERED_ID + "@ \r\n "; q += ", @" + INVOICEITEMS.LOCKED + "@ \r\n "; q += " ) \r\n "; q = AttachParams(q, v); dalsession.Execute(q); }
private static string AttachParams(string q, Invoiceitems v) { q = q.Replace("@" + INVOICEITEMS.INVOICE_ID + "@", "" + ConvertTO.SqlString(v.Invoice_id) + ""); q = q.Replace("@" + INVOICEITEMS.PO_NO + "@", "" + ConvertTO.SqlString(v.Po_no) + ""); q = q.Replace("@" + INVOICEITEMS.DC_NO + "@", "" + ConvertTO.SqlString(v.Dc_no) + ""); q = q.Replace("@" + INVOICEITEMS.PRODUCT_ID + "@", "" + ConvertTO.SqlString(v.Product_id) + ""); q = q.Replace("@" + INVOICEITEMS.SIZES_ID + "@", "" + ConvertTO.SqlString(v.Sizes_id) + ""); q = q.Replace("@" + INVOICEITEMS.COLOURS_ID + "@", "" + ConvertTO.SqlString(v.Colours_id) + ""); q = q.Replace("@" + INVOICEITEMS.QTY + "@", "" + ConvertTO.SqlString(v.Qty) + ""); q = q.Replace("@" + INVOICEITEMS.PRICE + "@", "" + ConvertTO.SqlString((v.Price) + "")); q = q.Replace("@" + INVOICEITEMS.CGST_PERCENT + "@", "" + ConvertTO.SqlString((v.Cgst_percent) + "")); q = q.Replace("@" + INVOICEITEMS.SGST_PERCENT + "@", "" + ConvertTO.SqlString((v.Sgst_percent) + "")); q = q.Replace("@" + INVOICEITEMS.IGST_PERCENT + "@", "" + ConvertTO.SqlString((v.Igst_percent) + "")); q = q.Replace("@" + INVOICEITEMS.REFERED_ID + "@", "" + ConvertTO.SqlString(v.Refered_id) + ""); q = q.Replace("@" + INVOICEITEMS.LOCKED + "@", "" + ConvertTO.SqlString(v.Locked) + ""); return(q); }
private static List <Invoiceitems> GetOldItemsData(int id, string OldDB) { string q = "USE " + OldDB + "; "; q += " SELECT " + INVOICEITEMS.INVOICEITEMS_TBL + ".* \r\n"; q += ", " + PRODUCT.PRODUCT_TBL + "." + PRODUCT.PRODUCT_NAME + " \r\n"; q += ", " + SIZES.SIZES_TBL + "." + SIZES.SIZES_NAME + " \r\n"; q += " FROM " + INVOICEITEMS.INVOICEITEMS_TBL + " \r\n"; q += " INNER JOIN " + PRODUCT.PRODUCT_TBL + "\r\n"; q += " ON(" + INVOICEITEMS.INVOICEITEMS_TBL + "." + INVOICEITEMS.PRODUCT_ID + " = " + PRODUCT.PRODUCT_TBL + "." + PRODUCT.PRODUCT_ID + " )\r\n"; q += " INNER JOIN " + SIZES.SIZES_TBL + "\r\n"; q += " ON(" + INVOICEITEMS.INVOICEITEMS_TBL + "." + INVOICEITEMS.SIZES_ID + " = " + SIZES.SIZES_TBL + "." + SIZES.SIZES_ID + " )\r\n"; q += " WHERE " + INVOICEITEMS.INVOICEITEMS_TBL + "." + INVOICE.INVOICE_ID + " = '" + id + "' \r\n"; q += " ORDER BY " + INVOICEITEMS.INVOICEITEMS_TBL + "." + INVOICEITEMS.INVOICEITEMS_ID + ";\r\n"; List <Invoiceitems> list = new List <Invoiceitems>(); using (IDataReader redr = new DAL().Listreader(q)) { while (redr.Read() == true) { Invoiceitems obj = new Invoiceitems() { Invoiceitems_id = "", Invoice_id = "", Po_no = "", Dc_no = "", Product_id = (redr[PRODUCT.PRODUCT_NAME].ToString()), Sizes_id = (redr[SIZES.SIZES_NAME].ToString()), Colours_id = "1", Qty = redr[INVOICEITEMS.QTY].ToString(), Price = ConvertTO.Decimal(redr[INVOICEITEMS.PRICE].ToString()), Cgst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.CGST_PERCENT].ToString()), Sgst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.SGST_PERCENT].ToString()), Igst_percent = ConvertTO.Decimal(redr[INVOICEITEMS.IGST_PERCENT].ToString()), Refered_id = "0", Locked = "0", }; list.Add(obj); } return(list); } }
public static void Insert(List <Invoiceitems> list, DAL dalsession) { for (int i = 0; i < list.Count; i++) { Invoiceitems obj = new Invoiceitems() { Invoice_id = list[i].Invoice_id, Po_no = list[i].Po_no, Dc_no = list[i].Dc_no, Product_id = list[i].Product_id, Sizes_id = list[i].Sizes_id, Colours_id = list[i].Colours_id, Qty = list[i].Qty, Price = list[i].Price, Cgst_percent = list[i].Cgst_percent, Sgst_percent = list[i].Sgst_percent, Igst_percent = list[i].Igst_percent, Refered_id = list[i].Refered_id, Locked = list[i].Locked }; InsertSub(obj, dalsession); } }
private static void InsertData(Invoice.Const.Invoice obj, List <Invoiceitems> items, string newDB) { DAL dalsession; try { dalsession = new DAL(); dalsession.BeginTransaction(); List <Invoiceitems> list_new = new List <Invoiceitems>(); if (obj != null) { if (CheckData(obj, newDB) != true) { Invoice.Const.Invoice obj_new = new Invoice.Const.Invoice { Uniqueno = obj.Uniqueno, Company_id = obj.Company_id, Acy_id = obj.Acy_id, Order_id = "1", Invoice_no = obj.Invoice_no, Invoice_date = obj.Invoice_date, Party_id = CParty_exten.GetId_Name(obj.Party_id), Taxtype_id = CTaxtype_exten.GetId_Name(obj.Taxtype_id), Salestype_id = CSalestype_exten.GetId_Name(obj.Salestype_id), Transport_id = CTransport_exten.GetId_Name(obj.Transport_id), Deliveredto_id = CDeliveredto_exten.GetId_Name(obj.Deliveredto_id), Bundel = obj.Bundel, Total_qty = obj.Total_qty, Total_taxable_amount = obj.Total_taxable_amount, Total_cgst = obj.Total_cgst, Total_sgst = obj.Total_sgst, Total_igst = obj.Total_igst, Gsttotal = obj.Gsttotal, Ledger_id = CLedger_exten.GetName_Id(obj.Ledger_id), Additional = obj.Additional, Rounds = obj.Rounds, Grandtotal = obj.Grandtotal, Refered_amt = obj.Refered_amt, Locked = obj.Locked, Notes = obj.Notes, Active_id = obj.Active_id, User_id = "1" }; //switch (obj.Company_id) //{ // case "1": // obj_new.Company_id = "1"; // obj_new.Acy_id = "1"; // break; // case "3": // obj_new.Company_id = "1"; // obj_new.Acy_id = "2"; // break; // case "4": // obj_new.Company_id = "1"; // obj_new.Acy_id = "3"; // break; //} obj_new.Uniqueno = obj_new.Acy_id + "~" + obj_new.Company_id + "~" + obj_new.Invoice_no; Invoice.Class.CInvoice.Insert(obj_new, dalsession); var vId = DAL.GetIdentity(dalsession); for (int k = 0; k < items.Count; k++) { Invoiceitems v = new Invoiceitems() { Invoice_id = vId, Po_no = "", Dc_no = "", Product_id = CProduct_exten.GetId_Name(items[k].Product_id), Sizes_id = CSizes_exten.GetId_Name(items[k].Sizes_id), Colours_id = "1", Qty = items[k].Qty, Price = items[k].Price, Cgst_percent = items[k].Cgst_percent, Sgst_percent = items[k].Sgst_percent, Igst_percent = items[k].Igst_percent, Refered_id = items[k].Refered_id, Locked = items[k].Locked }; list_new.Add(v); } Invoice.Class.CInvoiceitems.Insert(list_new, dalsession); dalsession.Commit(); } } } catch (Exception ex) { MessageBox.Show("error : " + ex); } }