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);
}
}
