public DsReports.DailySalesDataTable GetDailySales(decimal closingdiff,DateTime d1,DateTime d2,bool isdate,bool useconstr=false,string newconstr="p")
{
var ds = ReportsDataManager.GetDailySalesReport(d1,d2,isdate,useconstr,newconstr);
var ds1 = new DsReports();
var dt = ds1.DailySales;
CreateFixDailySales(ref dt);
foreach (DataRow dr1 in ds.Rows)
{
// var tot = 0m;
var drs = dt.Select("Ctype = " + int.Parse(dr1["amounttype"].ToString()) + "");
// tot += decimal.Parse(dr1["amount"].ToString());
foreach (var dr in drs)
{
var scode = int.Parse(dr1["shiftcode"].ToString());
if (scode == 1 || scode == 0)
{
dr["Shift1"] = dr1["amount"];
// dr["Total"] = tot;
}
if (scode == 2)
{
dr["Shift2"] = dr1["amount"];
//tot += decimal.Parse(dr1["amount"].ToString());
// dr["Total"] = tot;
}
if (scode == 3)
{
dr["Shift3"] = decimal.Parse(dr1["amount"].ToString()).ToString("N2");
// tot += decimal.Parse(dr1["amount"].ToString());
// dr["Total"] = tot;
}
}
}
InsertSummaryRow(ref dt);
AddProfitPer(ref dt);
//GetCustomerCount
AddFooter(ref dt,d1,isdate,useconstr,newconstr);
if (closingdiff > 0)
{
var ndr = dt.NewRow();
ndr["Ctype"] = 16;
ndr["Shift1"] = closingdiff;
dt.Rows.Add(ndr);
}
var ndr1 = dt.NewRow();
ndr1["Ctype"] = 20;
ndr1["Shift1"] = ReportsDataManager.GetCustomerCount(d1, d2, isdate,useconstr,newconstr);
dt.Rows.Add(ndr1);
return dt;
}
public StockList(DataTable ds, string itemno, string whid, bool isstore = false,bool iszeroqty=false)
: this()
{
var dsS = new DsReports().GetStockItemsForTransfer;
if (!isstore)
{
dsS = ClsGetData.GetStockItemsForTransferDataTable(Utils.Company,
whid,iszeroqty);
getStockListBindingSource.DataSource = dsS;
}
else
{
dsS = ClsGetData.GetStockItemsForTransferDataTable(Utils.Company,
Utils.SalesStoreCode,iszeroqty);
getStockListBindingSource.DataSource = dsS;
}
if (ds != null)
{
foreach (DataRow d in ds.Rows)
{
if (d.RowState != DataRowState.Deleted)
{
var rows = dsS.Select("STOCKID = " + int.Parse(d["STOCKID"].ToString()) + "");
foreach (DataRow dr in rows)
{
dr["SEL"] = bool.TrueString;
}
}
}
}
}
public static DsReports.SalesAmountDetailsCreditDataTable SalesAmountDetailsCredit(DateTime d1, DateTime d2, bool bydate = false, bool useconstr = false, string newconstr = "p")
{
var ta = new SalesAmountDetailsCreditTableAdapter { Connection = { ConnectionString = useconstr ? newconstr : _constr } };
var ds = new DsReports().SalesAmountDetailsCredit;
if(!bydate)
ta.Fill(ds);
else
{
ta.FillByDate(ds, d1, d2);
}
return ds;
}
void AddFooter(ref DsReports.DailySalesDataTable dt ,DateTime d1,bool isdate,bool useconstr=false,string newconstr="p")
{
var cprice = isdate ? GetSumCprice(d1, useconstr, newconstr) : GetSumCprice(useconstr, newconstr);
var sprice = isdate ? GetSumSprice(d1, useconstr, newconstr) : GetSumSprice(useconstr, newconstr);
var begcas = GetLastClosingAmt(d1,isdate);
var cashonhand = 0m;
foreach (DataRow d in dt.Rows)
{
if (d["Ctype"].ToString() == "1")
if(!string.IsNullOrEmpty(d["TOTAL"].ToString()))
cashonhand = decimal.Parse(d["TOTAL"].ToString());
}
var ndr = dt.NewRow();
ndr["Ctype"] = 11;
ndr["Shift1"] = cprice;
ndr["Shift3"] = "Sale value";
ndr["TOTAL"] = sprice;
dt.Rows.Add(ndr);
//dt.Rows.InsertAt(ndr, 12);
//ndr = dt.NewRow();
//ndr["Ctype"] = 12;
//ndr["Shift1"] = sprice;
//dt.Rows.Add(ndr);
//dt.Rows.InsertAt(ndr, 13);
ndr = dt.NewRow();
ndr["Ctype"] = 13;
ndr["Shift1"] = begcas;
ndr["Shift3"] = "Cash on hand";
ndr["TOTAL"] = cashonhand + begcas;
dt.Rows.Add(ndr);
//dt.Rows.InsertAt(ndr, 14);
//ndr = dt.NewRow();
// ndr["Ctype"] = 14;
// ndr["Shift1"] = cashonhand + begcas;
// dt.Rows.Add(ndr);
// dt.Rows.InsertAt(ndr, 15);
}
public DsReports.RptItemPurchaseDetailsDataTable GetRptItemPurchaseDetails(string itemno,DateTime d1,DateTime d2)
{
var ta = new RptItemPurchaseDetailsTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().RptItemPurchaseDetails;
ta.FillRptItemPurchaseDetails(ds, itemno, d1, d2);
return ds;
}
public DsReports.PurchaseBonusDataTable GetPurchaseBonus(DateTime d1,DateTime d2)
{
var ta = new PurchaseBonusTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().PurchaseBonus;
ta.FillByBonus(ds, d1, d2);
return ds;
}
public int SaveCosolidateInventoryRun(DsReports.CONSOLIDATEINVENTORYDataTable dt)
{
return new DsReportsTableAdapters.CONSOLIDATEINVENTORYTableAdapter {Connection = {ConnectionString = _constr}}.Update(dt);
}
public static DsReports.GetSalesTargetDetFSelStoreDataTable GetSalesTargetDetFSelStore(string commid, int empid, bool useconstr = false, string newconstr = "p")
{
var ds = new DsReports().GetSalesTargetDetFSelStore ;
var ta = new GetSalesTargetDetFSelStoreTableAdapter { Connection = { ConnectionString = useconstr ? newconstr : _constr } };
ta.Fill(ds, commid,empid);
return ds;
}
public static decimal GetTransferQty(string itemno, DateTime d1, DateTime d2)
{
var ta = new GetTQtyTableAdapter() { Connection = { ConnectionString = _constr } };
var ds = new DsReports().GetTQty;
ta.FillTQty(ds, d1, d2, itemno,Utils.Company);
decimal ret = 0m;
foreach (DataRow d in ds.Rows)
{
ret = decimal.Parse(d[0].ToString());
}
return ret;
}
public DsReports.RptPurchaseReturnDataTable RptPurchaseInv(string purinv, string comp)
{
var ta = new RptPurchaseReturnTableAdapter{Connection = {ConnectionString = _constr}};
var ds = new DsReports();
ta.Fill(ds.RptPurchaseReturn, purinv);
return ds.RptPurchaseReturn;
}
public DsReports.APARMAIN1DataTable GetPurchaseLedger(string supid, int duedays, int option)
{
var ta = new APARMAIN1TableAdapter {Connection = {ConnectionString = _constr}};
var dt = new DsReports().APARMAIN1;
if (option==0)
{
ta.Fill(dt);
}
if(option ==1)
{
ta.FillByOnlyDueDays(dt, duedays);
}
if (option == 2)
{
ta.FillByOnlySuplier(dt, supid);
}
if (option == 3)
{
ta.FillByDueAndSup(dt, duedays, supid);
}
return dt;
}
void CreateFixDailySales(ref DsReports.DailySalesDataTable dt)
{
for (int i = 1; i < 11; i++)
{
var dr = dt.NewRow();
dr["Ctype"] = i;
dt.Rows.Add(dr);
}
}
public DsReports.ITEMTRANSFERMAINLISTDataTable ItemTranferMainListForHist(bool frmstore, DateTime d1,
DateTime d2, bool useothercomp = false, string concomp = "P", string concode = "")
{
var ta = new ITEMTRANSFERMAINLISTTableAdapter
{
Connection = {ConnectionString = useothercomp ? concomp : _constr}
};
var ds = new DsReports().ITEMTRANSFERMAINLIST;
if (frmstore)
{
ta.FillByTransferByDate(ds, useothercomp ? concode : Utils.Company, d1, d2);
}
else
{
ta.FillByReceivedByDate(ds, useothercomp ? concode : Utils.Company, d1, d2);
}
return ds;
}
void AddProfitPer(ref DsReports.DailySalesDataTable dt )
{
decimal p1 = 0m;
decimal p2 = 0m;
decimal p3 = 0m;
decimal ptot = 0m;
foreach (DataRow dr in dt.Rows)
{
if (dr["Ctype"].ToString() == "9")
{
if (!string.IsNullOrEmpty(dr["Shift1"].ToString()))
p1 += decimal.Parse(dr["Shift1"].ToString());
if (!string.IsNullOrEmpty(dr["Shift2"].ToString()))
p2 += decimal.Parse(dr["Shift2"].ToString());
if (!string.IsNullOrEmpty(dr["Shift3"].ToString()))
p3 += decimal.Parse(dr["Shift3"].ToString());
if (!string.IsNullOrEmpty(dr["Total"].ToString()))
ptot += decimal.Parse(dr["Total"].ToString());
}
}
foreach (DataRow dr in dt.Rows)
{
if (dr["Ctype"].ToString() == "10")
{
if (s1 != 0)
{
dr["Shift1"] = (p1/s1)*100;
ptot =(p1 / s1) * 100;
}
if (s2 != 0)
{
dr["Shift2"] = (p2/s2)*100;
ptot += (p2 / s2) * 100;
}
if (s3 != 0)
{
dr["Shift3"] = ((p3/s3)*100).ToString("N2");
ptot += (p3 / s3) * 100;
}
if (ptot != 0)
{
dr["Total"] = ptot;
if(p1 >0 && p2 > 0 && p3 >0)
dr["Total"] = ptot/3;
if (p1 > 0 && p2 > 0 && p3 == 0)
dr["Total"] = ptot / 2;
if (p1 > 0 && p2 == 0 && p3 == 0)
dr["Total"] = ptot ;
if (p1 == 0 && p2 > 0 && p3 == 0)
dr["Total"] = ptot;
if (p1 == 0 && p2 == 0 && p3 > 0)
dr["Total"] = ptot;
}
}
}
}
void InsertSummaryRow(ref DsReports.DailySalesDataTable dt )
{
//var ndr = dt.NewRow();
//ndr["Ctype"] = 4;
var tots = 0m;
foreach (DataRow dr in dt.Rows)
{
if (int.Parse(dr["ctype"].ToString()) < 11)
{
if (!string.IsNullOrEmpty(dr["Shift1"].ToString()))
{
tots += decimal.Parse(dr["Shift1"].ToString());
}
if (!string.IsNullOrEmpty(dr["Shift2"].ToString()))
{
tots += decimal.Parse(dr["Shift2"].ToString());
}
if (!string.IsNullOrEmpty(dr["Shift3"].ToString()))
{
tots += decimal.Parse(dr["Shift3"].ToString());
}
if(tots!=0)
dr["Total"] = tots;
tots = 0;
// if (!string.IsNullOrEmpty(dr["Total"].ToString()))
// tot += decimal.Parse(dr["Total"].ToString());
}
}
foreach (DataRow dr in dt.Rows)
{
if (int.Parse(dr["ctype"].ToString()) < 4)
{
if (!string.IsNullOrEmpty(dr["Shift1"].ToString()))
{
s1 += decimal.Parse(dr["Shift1"].ToString());
//tots += decimal.Parse(dr["Shift1"].ToString());
}
if (!string.IsNullOrEmpty(dr["Shift2"].ToString()))
{
s2 += decimal.Parse(dr["Shift2"].ToString());
//tots += decimal.Parse(dr["Shift2"].ToString());
}
if (!string.IsNullOrEmpty(dr["Shift3"].ToString()))
{
s3 += decimal.Parse(dr["Shift3"].ToString());
//tots += decimal.Parse(dr["Shift3"].ToString());
}
}
}
foreach (DataRow dr in dt.Rows)
{
if (int.Parse(dr["ctype"].ToString()) == 4)
{
dr["Shift1"] = s1;
dr["Shift1"] = s1;
dr["Shift2"] = s2;
dr["Shift3"] = s3.ToString("N2");
dr["Total"] = s1 + s2 + s3;
}
}
// ndr["Shift1"] = s1;
// ndr["Shift2"] = s2;
// ndr["Shift3"] = s3;
// ndr["Total"] = tot;
//// dt.Rows.Add(ndr);
// dt.Rows.InsertAt(ndr,3);
}
public DsReports.ItemsListDataTable GetRptItemsList()
{
var ta = new DsReportsTableAdapters.ItemsListTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().ItemsList;
ta.Fill(ds);
return ds;
}
public DsReports.ITEMLISTFORCONSOLIDATEINVDataTable ItemListForConsolidateInv(int option,string perm)
{
var ret = new DsReports().ITEMLISTFORCONSOLIDATEINV;
if (option==0)
new DsReportsTableAdapters.ITEMLISTFORCONSOLIDATEINVTableAdapter { Connection = {ConnectionString = _constr}}.Fill(ret);
if(option==1)
new DsReportsTableAdapters.ITEMLISTFORCONSOLIDATEINVTableAdapter { Connection = { ConnectionString = _constr } }.FillByGroupId(ret, int.Parse(perm));
if(option==2)
new DsReportsTableAdapters.ITEMLISTFORCONSOLIDATEINVTableAdapter { Connection = { ConnectionString = _constr } }.FillByItemNo(ret,perm,perm);
if (option == 3)
new DsReportsTableAdapters.ITEMLISTFORCONSOLIDATEINVTableAdapter { Connection = { ConnectionString = _constr } }.FillBySupplier(ret, perm);
return ret;
}
public DsReports.GetDataByItemGroupDataTable GetDataByItemGroup(int groupid,DateTime d1,DateTime d2)
{
var ta = new GetDataByItemGroupTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().GetDataByItemGroup;
ta.FillByItemGroup(ds, groupid, d1, d2);
return ds;
}
public DsReports.GetItemGroupAllDataTable GetItemGroupAll(DateTime d1,DateTime d2)
{
var ta = new GetItemGroupAllTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().GetItemGroupAll;
ta.FillByItemGroupAll(ds, d1, d2);
return ds;
}
public DsReports.DailySalesDataTable GetCreditDetails(DateTime d1, DateTime d2, bool isdate, bool useconstr = false, string newconstr = "p")
{
var ds = ReportsDataManager.SalesAmountDetailsCredit(d1,d2,isdate,useconstr,newconstr);
var ds1 = new DsReports();
var dt = ds1.DailySales;
//for (int i = 1; i < 4; i++)
//{
// var dr = dt.NewRow();
// dr["Ctype"] = i;
// dt.Rows.Add(dr);
//}
var dr = dt.NewRow();
dt.Rows.Add(GetDataRow(ds, "A", "2", dr, true));
dr = dt.NewRow();
dt.Rows.Add(GetDataRow(ds, "C", "3", dr));
dr = dt.NewRow();
dt.Rows.Add(GetDataRow(ds, "I", "3", dr));
return dt;
}
public Ledgers.ARAPRECEIPTSDETAILSDataTable CreatePayeMentList(DsReports.APARMAIN1DataTable dt)
{
var ret = new Ledgers.ARAPRECEIPTSDETAILSDataTable ();
foreach (DsReports.APARMAIN1Row dr in dt.Rows)
{
var newrow = (Ledgers.ARAPRECEIPTSDETAILSRow) ret.NewRow();
newrow.DUEDAYS = dr.DUEDAYS;
newrow.REFNO = dr.REFNO;
newrow.TRANID = dr.TRANID;
newrow.INVOICEDATE = dr.INVOICEDATE;
newrow.INVOICENO = dr.INVOICENO;
newrow.INVAMOUNT = dr.INVAMOUNT;
newrow.PAIDAMOUNT = dr.PAIDAMOUNT;
newrow.BALANCE = dr.BALANCE;
newrow.DISCOUNT = dr.DISCOUNT;
ret.Rows.Add(newrow);
}
return ret;
}
public static int SaveCosolidateInventoryRun(DsReports.CONSOLIDATEINVENTORYDataTable dt)
{
return new StockMaster().SaveCosolidateInventoryRun(dt);
}
public static decimal GetSalesQty(string itemno, DateTime d1, DateTime d2,bool isreturn=false)
{
var ta = new GetSQtyTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().GetSQty;
ta.FillSQty(ds, d1, d2, itemno, isreturn) ;
decimal ret = 0m;
foreach (DataRow d in ds.Rows)
{
ret = decimal.Parse(d[0].ToString());
}
return ret;
}
//SalesAmountDetailsCredit
public static DsReports.GetItemMoveDataTable GetItemMove (string itemname,string itemno,DateTime d1,DateTime d2)
{
var ta = new GetItemMoveTableAdapter { Connection = { ConnectionString = _constr } };
var ds = new DsReports().GetItemMove;
ta.Fill(ds, itemname, itemno, d1, d2, Utils.Company);
return ds;
}
public static DsReports.SalesTargetFrmDtToDtDataTable SalesTargetFrmDtToDate(DateTime d1, DateTime d2, int empid,
string empno, string empname, string stye, string constr)
{
var ds = new DsReports().SalesTargetFrmDtToDt;
var ta = new SalesTargetFrmDtToDtTableAdapter { Connection = { ConnectionString = constr } };
ta.Fill(ds, empname, d1, d2, empid, stye, empno);
return ds;
}
public DsReports.ITEMTRANSFERMAINLISTDataTable ItemTranferMainListForHist(bool frmstore)
{
var ta = new ITEMTRANSFERMAINLISTTableAdapter {Connection = {ConnectionString = _constr}};
var ds = new DsReports().ITEMTRANSFERMAINLIST;
if (frmstore)
{
ta.FillByTransfer(ds, Utils.Company);
}
else
{
ta.Fill(ds, Utils.Company);
}
return ds;
}
public DsReports.SalesInvListDataTable GetSalesReport(string invno = null)
{
var ta = new DsReportsTableAdapters.taSalesInvList {Connection = {ConnectionString = _constr}};
var ds = new DsReports().SalesInvList;
if (invno == null)
ta.Fill(ds, false);
else
{
ta.FillBySalesInv(ds, true, false, invno);
}
return ds;
}
public decimal GetTranferCostValue(string tranid, bool useothercomp = false, string concomp = "P")
{
decimal retval = 0;
var ta = new GetTranferCostValueTableAdapter
{
Connection = {ConnectionString = useothercomp ? concomp : _constr}
};
var ds = new DsReports().GetTranferCostValue;
ta.Fill(ds, tranid);
foreach (DataRow dr in ds.Rows)
{
retval = decimal.Parse(dr[0].ToString());
}
return retval;
}
public DsReports.RptPOMainDataTable DoPrint(string pono)
{
var ta = new DsReportsTableAdapters.RptPOMain {Connection = {ConnectionString = _constr}};
var ds = new DsReports().RptPOMain;
ta.Fill(ds, pono);
return ds;
}
public DsReports.SalesInvListDataTable GetSalesReportByDate(DateTime d1,DateTime d2)
{
var ds = new DsReports().SalesInvList;
try
{
var ta = new DsReportsTableAdapters.taSalesInvList { Connection = { ConnectionString = _constr } };
ta.FillByTrandate(ds, false, d1, d2);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return ds;
}
