public Seachcode(Viewtable Fromviewable, string tablename)
{
InitializeComponent();
this.Fromviewable = Fromviewable;
this.tablename = tablename;
}
private void button7_Click_1(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_kacontractbegindatadetail in dc.tbl_kacontractbegindatadetails
select tbl_kacontractbegindatadetail;
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "List Detail Master Contract ", 3);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button4_Click_1(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_kacontractsdatadetail in dc.tbl_kacontractsdatadetails
// where tbl_kacontractsdatadetail.Constatus == "ALV"
select tbl_kacontractsdatadetail;
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "List All Contract Detail", 3);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button8_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_kacontractsdatadetail in dc.tbl_kacontractsdatadetails
where tbl_kacontractsdatadetail.Constatus == "ALV"
select new
{
ContractNo = tbl_kacontractsdatadetail.ContractNo,
Region = tbl_kacontractsdatadetail.SalesOrg,
Constatus = tbl_kacontractsdatadetail.Constatus,
Contracttype = tbl_kacontractsdatadetail.ConType,
EffFrm = tbl_kacontractsdatadetail.EffFrm,
EffTo = tbl_kacontractsdatadetail.EffTo,
CustomerCode = tbl_kacontractsdatadetail.Customercode,
CustomerName = tbl_kacontractsdatadetail.Fullname,
EftNoOfMonth = tbl_kacontractsdatadetail.EftNoOfMonth,
CurrentMonth = tbl_kacontractsdatadetail.CurrentMonth,
PCVolAched = tbl_kacontractsdatadetail.PCVolAched,
UCVolAched = tbl_kacontractsdatadetail.UCVolAched,
LitAched = tbl_kacontractsdatadetail.LitAched,
FTNAched = tbl_kacontractsdatadetail.ECAched,
NSRAched = tbl_kacontractsdatadetail.NSRAched,
AccruedAmt = tbl_kacontractsdatadetail.AccruedAmt,
PayControl = tbl_kacontractsdatadetail.PayControl,
Description = tbl_kacontractsdatadetail.Description,
PrdGrp = tbl_kacontractsdatadetail.PrdGrp,
FundPercentage = tbl_kacontractsdatadetail.FundPercentage,
SponsoredAmtperPC = tbl_kacontractsdatadetail.SponsoredAmtperPC,
AccruedDate = tbl_kacontractsdatadetail.AccruedDate,
FullCommitment = tbl_kacontractsdatadetail.SponsoredAmt,
CommitmentCurrent = tbl_kacontractsdatadetail.SponsoredTotal,
TotalPaid = tbl_kacontractsdatadetail.PaidAmt,
Balance = tbl_kacontractsdatadetail.Balance, //(to be accrual)
};
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "List Contracts Detail Alive", 4);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button14_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
string username = Utils.getusername();
LinqtoSQLDataContext da = new LinqtoSQLDataContext(connection_string);
var regioncode = (from tbl_Temp in da.tbl_Temps
where tbl_Temp.username == username
select tbl_Temp.RegionCode).FirstOrDefault();
var rscustemp2 = from tbl_kacontractsdatadetail in da.tbl_kacontractsdatadetails
where tbl_kacontractsdatadetail.SponsoredTotal > tbl_kacontractsdatadetail.PaidAmt
&& (from Tka_RegionRight in da.Tka_RegionRights
where Tka_RegionRight.RegionCode == regioncode
select Tka_RegionRight.Region
).Contains(tbl_kacontractsdatadetail.SalesOrg)
select new
{
tbl_kacontractsdatadetail.SalesOrg,
tbl_kacontractsdatadetail.CommittedDate,
tbl_kacontractsdatadetail.ContractNo,
tbl_kacontractsdatadetail.ConType,
tbl_kacontractsdatadetail.Constatus,
tbl_kacontractsdatadetail.Customercode,
tbl_kacontractsdatadetail.Fullname,
tbl_kacontractsdatadetail.Address,
tbl_kacontractsdatadetail.PayType,
tbl_kacontractsdatadetail.PayControl,
tbl_kacontractsdatadetail.Description,
Achived = tbl_kacontractsdatadetail.SponsoredTotal,
Paid = tbl_kacontractsdatadetail.PaidAmt,
Unpaid = tbl_kacontractsdatadetail.SponsoredTotal - tbl_kacontractsdatadetail.PaidAmt,
tbl_kacontractsdatadetail.EffFrm,
tbl_kacontractsdatadetail.EffTo,
tbl_kacontractsdatadetail.Remark,
};
Viewtable viewtbl = new Viewtable(rscustemp2, da, "List Detail Unpaid reports", 33);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button2_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.CommandTimeout = 0;
// select tblEDLP;
string username = Utils.getusername();
dc.ExecuteCommand("DELETE FROM tbl_MassContractChangeTemp where tbl_MassContractChangeTemp.Username ='******' or tbl_MassContractChangeTemp.Username is null");
dc.SubmitChanges();
Model.MassConfirm MassConfirm = new Model.MassConfirm();
// slmodel.deleteedlp();
// string connection_string = Utils.getConnectionstr();
// var db = new LinqtoSQLDataContext(connection_string);
// string username = Utils.getusername();
// var rs = from tblEDLP in db.tblEDLPs
// confirmmodel.
// Model.Salesinput_ctrl slmodel = new Model.Salesinput_ctrl();
string contracts = cb_contractstatus.Text;
MassConfirm.massfileContractchangeinput(contracts);
var rs = from tbl_MassContractChangeTemp in dc.tbl_MassContractChangeTemps
where tbl_MassContractChangeTemp.Username == username
select tbl_MassContractChangeTemp;
if (rs.Count() > 0)
{
Viewtable viewtbl = new Viewtable(rs, dc, "CONTRACT LIST TO CHANGE STATUS", 12);// view code 11 la can viet them lenh MASS CONFIL
viewtbl.Show();
viewtbl.Focus();
}
// }
}
private void button15_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
string username = Utils.getusername();
var regioncode = (from tbl_Temp in dc.tbl_Temps
where tbl_Temp.username == username
select tbl_Temp.RegionCode).FirstOrDefault();
var liveandinregion = from tbl_kacontractdata in dc.tbl_kacontractdatas
where tbl_kacontractdata.Consts == "ALV"
&& (from Tka_RegionRight in dc.Tka_RegionRights
where Tka_RegionRight.RegionCode == regioncode
select Tka_RegionRight.Region
).Contains(tbl_kacontractdata.SalesOrg)
select tbl_kacontractdata.ContractNo;
var rscustemp2 = from tbl_kacontractsdetailpayment in dc.tbl_kacontractsdetailpayments
where tbl_kacontractsdetailpayment.PayControl == "REQ"
&& liveandinregion.Contains(tbl_kacontractsdetailpayment.ContractNo)
select new
{
tbl_kacontractsdetailpayment.ContractNo,
tbl_kacontractsdetailpayment.BatchNo,
ContracName = tbl_kacontractsdetailpayment.ContracName.Trim(),
tbl_kacontractsdetailpayment.PayType,
tbl_kacontractsdetailpayment.PayID,
Description = tbl_kacontractsdetailpayment.Description.Trim(),
tbl_kacontractsdetailpayment.PaidRequestAmt,
tbl_kacontractsdetailpayment.PayControl,
tbl_kacontractsdetailpayment.PrintDate,
Remark = tbl_kacontractsdetailpayment.Remark.Trim(),
tbl_kacontractsdetailpayment.CRDDAT,
tbl_kacontractsdetailpayment.CRDUSR,
tbl_kacontractsdetailpayment.SubID,
};
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "LIST PAYMENT REQUEST FOR APPROVAL", 3);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button4_Click(object sender, EventArgs e)
{
//LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
//var typeffmain = typeof(tbl_KaCustomer);
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_KaCustomer in dc.tbl_KaCustomers
select tbl_KaCustomer;
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "LIST CUSTOMER DATA", 3);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button10_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_kacontractdata in dc.tbl_kacontractdatas
where (tbl_kacontractdata.EftDate - DateTime.Today).Value.Days / 30 <= 3 && tbl_kacontractdata.Consts == "ALV"
select new
{
tbl_kacontractdata.ContractNo,
tbl_kacontractdata.SalesOrg,
tbl_kacontractdata.ConType,//contract type
tbl_kacontractdata.Consts, //contract status
tbl_kacontractdata.Currency,
Validfrom = tbl_kacontractdata.EffDate,
Validto = tbl_kacontractdata.EftDate,
tbl_kacontractdata.Customer,
tbl_kacontractdata.Fullname,
tbl_kacontractdata.Channel,
FullCommitment = tbl_kacontractdata.TotSponsoredcommit,
AchivedCommitment = tbl_kacontractdata.TotDeal,
tbl_kacontractdata.Tot_paid,
Balance = tbl_kacontractdata.TotDeal - tbl_kacontractdata.Tot_paid,
VolumeCommit = tbl_kacontractdata.VolComm,
tbl_kacontractdata.PCVolAched,
tbl_kacontractdata.NSRAched,
tbl_kacontractdata.UCVolAched,
tbl_kacontractdata.CRDDAT,
tbl_kacontractdata.CRDUSR,
};
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "Contracts Expire Next 3 Months ", 5);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button5_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_kaProductlist in dc.tbl_kaProductlists
select tbl_kaProductlist;
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "LIST PRODUCTS DATA", 3);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button1_Click(object sender, EventArgs e)
{
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "kaPriodpicker")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
View.kaPriodpicker kaPriodpicker = new View.kaPriodpicker();
kaPriodpicker.ShowDialog();
string priod = kaPriodpicker.priod;
var rs = from tbl_kasale in dc.tbl_kasales
where tbl_kasale.Priod == priod
group tbl_kasale by new
{
tbl_kasale.Sold_to,
tbl_kasale.Sales_Org
}
into g
select new
{
Priod = g.Select(gg => gg.Priod).FirstOrDefault(),
Region = g.Key.Sales_Org,
Sold_to = g.Key.Sold_to,
Name = g.Select(gg => gg.Cust_Name).FirstOrDefault(),
PCs = g.Sum(gg => gg.EC).GetValueOrDefault(0),
EC = Math.Ceiling(g.Sum(gg => gg.PC).GetValueOrDefault(0)),
UC = Math.Ceiling(g.Sum(gg => gg.UC).GetValueOrDefault(0)),
Litter = Math.Ceiling(g.Sum(gg => gg.Litter).GetValueOrDefault(0)),
NSR = Math.Ceiling(g.Sum(gg => gg.NSR).GetValueOrDefault(0)),
GSR = Math.Ceiling(g.Sum(gg => gg.GSR).GetValueOrDefault(0)),
};
Viewtable viewtbl = new Viewtable(rs, dc, "SALES DATA PRIOD: " + priod, 2);// view code 1 la can viet them lenh
viewtbl.Show();
}
}
private void button11_Click_1(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
// UpdateControlTypeforPaymentrpt
#region ClearABbelanceZezoinFbl5n
SqlConnection conn2 = null;
SqlDataReader rdr1 = null;
string destConnString = Utils.getConnectionstr();
try
{
conn2 = new SqlConnection(destConnString);
conn2.Open();
SqlCommand cmd1 = new SqlCommand("UpdateControlTypeforPaymentrpt", conn2);
cmd1.CommandType = CommandType.StoredProcedure;
// cmd1.Parameters.Add("@name", SqlDbType.VarChar).Value = userupdate;
rdr1 = cmd1.ExecuteReader();
// rdr1 = cmd1.ExecuteReader();
}
finally
{
if (conn2 != null)
{
conn2.Close();
}
if (rdr1 != null)
{
rdr1.Close();
}
}
// MessageBox.Show("ok", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
#endregion
string username = Utils.getusername();
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "Fromdate to Date Select")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
View.KafromtoSelect kafromto = new View.KafromtoSelect();
kafromto.ShowDialog();
DateTime fromdate = kafromto.fromdate;
DateTime todate = kafromto.todate;
var regioncode = (from tbl_Temp in dc.tbl_Temps
where tbl_Temp.username == username
select tbl_Temp.RegionCode).FirstOrDefault();
var liveandinregion = from tbl_kacontractdata in dc.tbl_kacontractdatas
where (from Tka_RegionRight in dc.Tka_RegionRights
where Tka_RegionRight.RegionCode == regioncode
select Tka_RegionRight.Region
).Contains(tbl_kacontractdata.SalesOrg)
select tbl_kacontractdata.ContractNo;
var rscustemp2 = from tbl_kacontractsdetailpayment in dc.tbl_kacontractsdetailpayments
from tbl_kacontractdata in dc.tbl_kacontractdatas
where liveandinregion.Contains(tbl_kacontractsdetailpayment.ContractNo)
&& tbl_kacontractsdetailpayment.ContractNo == tbl_kacontractdata.ContractNo
&& tbl_kacontractsdetailpayment.PaidOn >= fromdate
&& tbl_kacontractsdetailpayment.PaidOn <= todate
select new
{
tbl_kacontractsdetailpayment.ContractNo,
Contract_status = tbl_kacontractdata.Consts,
Contract_Type = tbl_kacontractdata.ConType,
tbl_kacontractsdetailpayment.BatchNo,
ContracName = tbl_kacontractsdetailpayment.ContracName.Trim(),
tbl_kacontractsdetailpayment.PayType,
tbl_kacontractsdetailpayment.PayID,
Description = tbl_kacontractsdetailpayment.Description.Trim(),
tbl_kacontractsdetailpayment.PaidRequestAmt,
tbl_kacontractsdetailpayment.PayControl,
tbl_kacontractsdetailpayment.ControlType,
tbl_kacontractsdetailpayment.PrintDate,
Remark = tbl_kacontractsdetailpayment.Remark.Trim(),
tbl_kacontractsdetailpayment.CRDDAT,
tbl_kacontractsdetailpayment.CRDUSR,
tbl_kacontractsdetailpayment.SubID,
};
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "LIST ALL PAYMENT", 3);// view code 1 la can viet them lenh
viewtbl.Show();
}
}
private void button3_Click_1(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
string username = Utils.getusername();
#region update lai groupcode
SqlConnection conn2 = null;
SqlDataReader rdr1 = null;
string destConnString = Utils.getConnectionstr();
try
{
conn2 = new SqlConnection(destConnString);
conn2.Open();
SqlCommand cmd1 = new SqlCommand("UpdateCustGrcode", conn2);
cmd1.CommandType = CommandType.StoredProcedure;
// cmd1.Parameters.Add("@name", SqlDbType.VarChar).Value = userupdate;
cmd1.CommandTimeout = 0;
rdr1 = cmd1.ExecuteReader();
// rdr1 = cmd1.ExecuteReader();
}
finally
{
if (conn2 != null)
{
conn2.Close();
}
if (rdr1 != null)
{
rdr1.Close();
}
}
// MessageBox.Show("ok", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
#endregion
LinqtoSQLDataContext da = new LinqtoSQLDataContext(connection_string);
var regioncode = (from tbl_Temp in da.tbl_Temps
where tbl_Temp.username == username
select tbl_Temp.RegionCode).FirstOrDefault();
var rscustemp2 = from tbl_kacontractdata in da.tbl_kacontractdatas
where (from Tka_RegionRight in da.Tka_RegionRights
where Tka_RegionRight.RegionCode == regioncode
select Tka_RegionRight.Region
).Contains(tbl_kacontractdata.SalesOrg)
select new
{
tbl_kacontractdata.SalesOrg,
tbl_kacontractdata.ContractNo,
tbl_kacontractdata.ConType,
tbl_kacontractdata.Consts,
tbl_kacontractdata.Channel,
tbl_kacontractdata.Customer,
tbl_kacontractdata.ListCodeinGroup,
tbl_kacontractdata.Fullname,
//tbl_kacontractsdatadetail.Address,
//tbl_kacontractsdatadetail.PayType,
//tbl_kacontractsdatadetail.PayControl,
//tbl_kacontractsdatadetail.Description,
//Achived = tbl_kacontractsdatadetail.SponsoredTotal,
//Paid = tbl_kacontractsdatadetail.PaidAmt,
//Unpaid = tbl_kacontractsdatadetail.SponsoredTotal - tbl_kacontractsdatadetail.PaidAmt,
//tbl_kacontractsdatadetail.EffFrm,
//tbl_kacontractsdatadetail.EffTo,
//tbl_kacontractsdatadetail.Remark,
};
Viewtable viewtbl = new Viewtable(rscustemp2, da, "CONTRACTS WITH GROUP CODE", 555);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button1_Click(object sender, EventArgs e)
{
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "kaPriodpicker")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.CommandTimeout = 0;
View.kaPriodpicker kaPriodpicker = new View.kaPriodpicker();
kaPriodpicker.ShowDialog();
string priod = kaPriodpicker.priod;
bool chon = kaPriodpicker.kq;
if (chon)
{
#region view
var rs = from tbl_kasale in dc.tbl_kasales
where tbl_kasale.Priod == priod
select new
{
tbl_kasale.Priod,
tbl_kasale.Sales_District,
tbl_kasale.Sales_District_desc,
tbl_kasale.Sales_Org,
tbl_kasale.Sold_to,
tbl_kasale.Cust_Name,
tbl_kasale.Outbound_Delivery,
tbl_kasale.Key_Acc_Nr,
tbl_kasale.Delivery_Date,
tbl_kasale.Invoice_Doc_Nr,
tbl_kasale.Invoice_Date,
tbl_kasale.Currency,
tbl_kasale.Mat_Group,
tbl_kasale.Mat_Group_Text,
tbl_kasale.Mat_Number,
tbl_kasale.Mat_Text,
PCs = tbl_kasale.EC,
tbl_kasale.UoM,
EC = tbl_kasale.PC,
tbl_kasale.UC,
tbl_kasale.Litter,
tbl_kasale.GSR,
tbl_kasale.NSR,
tbl_kasale.Username,
tbl_kasale.id
};
Viewtable viewtbl = new Viewtable(rs, dc, "SALES DATA PRIOD: " + priod, 55);// view code 1 la can viet them lenh
viewtbl.ShowDialog();
#endregion
}
}
}
private void button1_Click_1(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.CommandTimeout = 0;
// select tblEDLP;
string username = Utils.getusername();
// tbl_Kasa
dc.ExecuteCommand("DELETE FROM tbl_kasalesTemp where tbl_kasalesTemp.Username = '******' or tbl_kasalesTemp.Username is null or tbl_kasalesTemp.Priod is null");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.SubmitChanges();
//tbl_kasalesTemp p = new tbl_kasalesTemp();
//p.Priod
dc.ExecuteCommand("DELETE FROM tbl_KaCustomertemp where tbl_KaCustomertemp.Username ='******' or tbl_KaCustomertemp.Username is null");
dc.SubmitChanges();
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "kaPriodpicker")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
View.kaPriodpicker kaPriodpicker = new View.kaPriodpicker();
// Datepick
kaPriodpicker.ShowDialog();
string priod = kaPriodpicker.priod;
DateTime fromdate = kaPriodpicker.fromdate;
DateTime todate = kaPriodpicker.todate;
//string connection_string = Utils.getConnectionstr();
//LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
//string username = Utils.getusername();
if (priod != "" && fromdate != todate && priod != null)
{
Model.Salesinput_ctrl slmodel = new Model.Salesinput_ctrl();
slmodel.COGSinput();
#region // giới hạn doc type chỉ chấp nhận 3 loại YPR0,YPRD,NETP
List <string> condtypelist = new List <string>();
condtypelist.Add("VPRS");
//#region // giới hạn doc type chỉ chấp nhận 3 loại YPR0,YPRD,NETP
//var rsdoc = (from tbl_kasalesTemp in dc.tbl_kasalesTemps
// where tbl_kasalesTemp.Username == username && !condtypelist.Contains(tbl_kasalesTemp.Cond_Type)
var rsdoc1 = (from tbl_kasalesTemp in dc.tbl_kasalesTemps
where tbl_kasalesTemp.Username == username && !condtypelist.Contains(tbl_kasalesTemp.Cond_Type)
select tbl_kasalesTemp).Take(10);
if (rsdoc1.Count() > 0)
{
Viewtable viewtbl2 = new Viewtable(rsdoc1, dc, "kHÔNG UPLOAD ĐƯỢC, CÓ CÁC DOC TYPE KHÔNG ĐÚNG KIỂU : VPRS ", 3);// view code 1 la can viet them lenh
viewtbl2.Show();
viewtbl2.Focus();
return;
}
#endregion
#region // list doc da không thuộc kỳ hiện thời post
var rsdoc = (from tbl_kasalesTemp in dc.tbl_kasalesTemps
where tbl_kasalesTemp.Username == username &&
((tbl_kasalesTemp.Invoice_Date <fromdate || tbl_kasalesTemp.Invoice_Date> todate))
select tbl_kasalesTemp).Take(5);
if (rsdoc.Count() > 0)
{
Viewtable viewtbl2 = new Viewtable(rsdoc, dc, "kHÔNG UPLOAD ĐƯỢC, CÓ CÁC DOC VÍ DỤ NHƯ CÁC DOC SAU DATE KHÔNG THUỘC PRIOD: " + priod, 3);// view code 1 la can viet them lenh
viewtbl2.Show();
viewtbl2.Focus();
return;
}
#endregion
#region// update cogs value, ĐỒNG THỜI ĐÁNH DAU CÁC DOC KO UP ĐUOCWJ
SqlConnection conn2 = null;
SqlDataReader rdr1 = null;
string destConnString = Utils.getConnectionstr();
try
{
conn2 = new SqlConnection(destConnString);
conn2.Open();
SqlCommand cmd1 = new SqlCommand("KAupdateCOGStemptable", conn2);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add("@priod", SqlDbType.VarChar).Value = priod;
cmd1.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
cmd1.CommandTimeout = 0;
rdr1 = cmd1.ExecuteReader();
// rdr1 = cmd1.ExecuteReader();
}
finally
{
if (conn2 != null)
{
conn2.Close();
}
if (rdr1 != null)
{
rdr1.Close();
}
}
MessageBox.Show("Upload done !", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
#endregion update pc, uc
}
}
}
private void button2_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.CommandTimeout = 0;
// select tblEDLP;
string username = Utils.getusername();
// tbl_Kasa
dc.ExecuteCommand("DELETE FROM tbl_kasalesTemp where tbl_kasalesTemp.Username = '******' or tbl_kasalesTemp.Username is null or tbl_kasalesTemp.Priod is null");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.SubmitChanges();
//tbl_kasalesTemp p = new tbl_kasalesTemp();
//p.Priod
dc.ExecuteCommand("DELETE FROM tbl_KaCustomertemp where tbl_KaCustomertemp.Username ='******' or tbl_KaCustomertemp.Username is null");
dc.SubmitChanges();
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "kaPriodpicker")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
View.kaPriodpicker kaPriodpicker = new View.kaPriodpicker();
// Datepick
kaPriodpicker.ShowDialog();
string priod = kaPriodpicker.priod;
DateTime fromdate = kaPriodpicker.fromdate;
DateTime todate = kaPriodpicker.todate;
bool chon = kaPriodpicker.kq;
if (chon)
{
if (priod != "" && fromdate != todate && priod != null)
{
Model.Salesinput_ctrl slmodel = new Model.Salesinput_ctrl();
slmodel.edlpinput();
List <string> condtypelist = new List <string>();
condtypelist.Add("YPR0");
condtypelist.Add("YPRD");
condtypelist.Add("NETP");
#region // giới hạn doc type chỉ chấp nhận 3 loại YPR0,YPRD,NETP
var rsdoc = (from tbl_kasalesTemp in dc.tbl_kasalesTemps
where tbl_kasalesTemp.Username == username && !condtypelist.Contains(tbl_kasalesTemp.Cond_Type)
select tbl_kasalesTemp).Take(10);
if (rsdoc.Count() > 0)
{
Viewtable viewtbl2 = new Viewtable(rsdoc, dc, "kHÔNG UPLOAD ĐƯỢC, CÓ CÁC DOC TYPE KHÔNG ĐÚNG KIỂU : YPR0, YPRD, NETP ", 3);// view code 1 la can viet them lenh
viewtbl2.Show();
viewtbl2.Focus();
return;
}
#endregion
#region // list doc da post
var rsdoc3 = (from tbl_kasalesTemp in dc.tbl_kasalesTemps
where tbl_kasalesTemp.Username == username &&
((tbl_kasalesTemp.Invoice_Date <fromdate || tbl_kasalesTemp.Invoice_Date> todate))
select tbl_kasalesTemp).Take(10);
if (rsdoc3.Count() > 0)
{
Viewtable viewtbl2 = new Viewtable(rsdoc3, dc, "kHÔNG UPLOAD ĐƯỢC, CÓ CÁC DOC DATE KHÔNG THUỘC PRIOD: " + priod, 3);// view code 1 la can viet them lenh
viewtbl2.Show();
viewtbl2.Focus();
return;
}
#endregion
#region // productnew
var da = new LinqtoSQLDataContext(connection_string);
da.ExecuteCommand("DELETE FROM tbl_kaProductlistemp where tbl_kaProductlistemp.Username ='******'");
da.SubmitChanges();
var rscustemp = from tbl_kasalesTemp in dc.tbl_kasalesTemps
where !(from tbl_kaProductlist in dc.tbl_kaProductlists
select tbl_kaProductlist.MatNumber).Contains(tbl_kasalesTemp.Mat_Number) && tbl_kasalesTemp.Mat_Number != null
group tbl_kasalesTemp by tbl_kasalesTemp.Mat_Number into g
select new
{
MatNumber = g.Key,
MatText = g.Select(gg => gg.Mat_Text).FirstOrDefault(),
UoM = g.Select(gg => gg.UoM).FirstOrDefault(),
Pcrate = 0,
Ucrate = 0
};
if (rscustemp.Count() > 0)
{
// var db = new LinqtoSQLDataContext(connection_string);
foreach (var item in rscustemp)
{
tbl_kaProductlistemp prduct = new tbl_kaProductlistemp();
prduct.MatNumber = item.MatNumber;
prduct.MatText = item.MatText;
prduct.UoM = item.UoM;
prduct.Pcrate = 0;
prduct.Ucrate = 0;
prduct.Username = username;
if (prduct.MatNumber != null)
{
da.tbl_kaProductlistemps.InsertOnSubmit(prduct);
da.SubmitChanges();
}
}
var typeffmain = typeof(tbl_kaProductlist);
var typeffsub = typeof(tbl_kaProductlistemp);
VInputchange inputcdata = new VInputchange("PRODUCT LIST", "LIST PRODUCT NOT IN MASTER DATAPRODUCT", dc, "tbl_kaProductlist", "tbl_kaProductlistemp", typeffmain, typeffsub, "id", "id", username);
inputcdata.Show();
inputcdata.Focus();
return;
}
#endregion product new //--------------------
#region// update pc, uc
SqlConnection conn2 = null;
SqlDataReader rdr1 = null;
string destConnString = Utils.getConnectionstr();
try
{
conn2 = new SqlConnection(destConnString);
conn2.Open();
SqlCommand cmd1 = new SqlCommand("KAupdateSalePC_UCtemptable", conn2);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add("@priod", SqlDbType.VarChar).Value = priod;
cmd1.CommandTimeout = 0;
rdr1 = cmd1.ExecuteReader();
// rdr1 = cmd1.ExecuteReader();
}
finally
{
if (conn2 != null)
{
conn2.Close();
}
if (rdr1 != null)
{
rdr1.Close();
}
}
// MessageBox.Show("ok", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
#endregion update pc, uc
#region // view sales volume
var rs = from tbl_kasalesTemp in dc.tbl_kasalesTemps
where tbl_kasalesTemp.Username == username && tbl_kasalesTemp.Priod == priod //&& tbl_kasalesTemp.Username == username
select new
{
tbl_kasalesTemp.Priod,
tbl_kasalesTemp.Sold_to,
tbl_kasalesTemp.Sales_Org,
tbl_kasalesTemp.Sales_District,
tbl_kasalesTemp.Sales_District_desc,
tbl_kasalesTemp.Cust_Name,
tbl_kasalesTemp.Outbound_Delivery,
tbl_kasalesTemp.Delivery_Date,
tbl_kasalesTemp.Invoice_Doc_Nr,
tbl_kasalesTemp.Invoice_Date,
tbl_kasalesTemp.Key_Acc_Nr,
tbl_kasalesTemp.Cond_Type,
tbl_kasalesTemp.Mat_Group,
tbl_kasalesTemp.Mat_Group_Text,
tbl_kasalesTemp.Mat_Number,
tbl_kasalesTemp.Mat_Text,
tbl_kasalesTemp.Currency,
PCs = tbl_kasalesTemp.EC,
tbl_kasalesTemp.UoM,
tbl_kasalesTemp.EmptyCountValue,
tbl_kasalesTemp.GSR,
tbl_kasalesTemp.Litter,
tbl_kasalesTemp.NETP,
tbl_kasalesTemp.NSR,
EC = tbl_kasalesTemp.PC,
tbl_kasalesTemp.UC,
tbl_kasalesTemp.Username,
tbl_kasalesTemp.id,
};
if (rs.Count() > 0)
{
Viewtable viewtbl = new Viewtable(rs, dc, "SALES DATA PRIOD: " + priod, 1);// view code 1 la can viet them lenh
viewtbl.Show();
viewtbl.Focus();
}
#endregion
}
}
}
}
private void button7_Click(object sender, EventArgs e)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
var rscustemp2 = from tbl_kacontractdata in dc.tbl_kacontractdatas
where tbl_kacontractdata.Consts == "ALV"
select new
{
tbl_kacontractdata.ContractNo,
tbl_kacontractdata.SalesOrg,
tbl_kacontractdata.ConType,//contract type
tbl_kacontractdata.Consts, //contract status
tbl_kacontractdata.Currency,
Validfrom = tbl_kacontractdata.EffDate,
Validto = tbl_kacontractdata.EftDate,
tbl_kacontractdata.Customer,
tbl_kacontractdata.Fullname,
tbl_kacontractdata.Channel,
FullCommitment = tbl_kacontractdata.TotSponsoredcommit,
AchivedCommitment = tbl_kacontractdata.TotDeal,
tbl_kacontractdata.Tot_paid,
Balance = tbl_kacontractdata.TotDeal - tbl_kacontractdata.Tot_paid,
VolumeCommit = tbl_kacontractdata.VolComm,
tbl_kacontractdata.PCVolAched,
tbl_kacontractdata.NSRAched,
tbl_kacontractdata.UCVolAched,
tbl_kacontractdata.CRDDAT,
tbl_kacontractdata.CRDUSR,
};
//Utils ut = new Utils();
//dt = ut.ToDataTable(db, rs);
//this.rs = rs;
//this.dataGridView1.DataSource = dt;
//// tbl_kacontractdata t = new tbl_kacontractdata();
//#region format
//// tbl_kacontractdata.ContractNo,
//// tbl_kacontractdata.SalesOrg,
//// tbl_kacontractdata.ConType,//contract type
//// tbl_kacontractdata.Consts, //contract status
//// tbl_kacontractdata.Currency,
//// Validfrom = tbl_kacontractdata.EffDate,
//// Validto = tbl_kacontractdata.EftDate,
//// tbl_kacontractdata.Customer,
//// tbl_kacontractdata.Fullname,
//// tbl_kacontractdata.Channel,
//// FullCommitment = tbl_kacontractdata.TotSponsoredcommit,
//this.dataGridView1.Columns["FullCommitment"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["FullCommitment"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//// AchivedCommitment = tbl_kacontractdata.TotDeal,
//this.dataGridView1.Columns["AchivedCommitment"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["AchivedCommitment"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//this.dataGridView1.Columns["AchivedCommitment"].HeaderText = "Achived Commitment";
//// tbl_kacontractdata.Tot_paid,
//this.dataGridView1.Columns["Tot_paid"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["Tot_paid"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//this.dataGridView1.Columns["Tot_paid"].HeaderText = "Total Paid";
//// Balance = tbl_kacontractdata.TotDeal - tbl_kacontractdata.Tot_paid,
//this.dataGridView1.Columns["Balance"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["Balance"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
// // this.dataGridView1.Columns["Balance"].HeaderText = "Total Paid";
// // VolumeCommit = tbl_kacontractdata.VolComm,
//this.dataGridView1.Columns["VolumeCommit"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["VolumeCommit"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//this.dataGridView1.Columns["VolumeCommit"].HeaderText = "Volume Commit";
//// tbl_kacontractdata.PCVolAched,
//this.dataGridView1.Columns["PCVolAched"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["PCVolAched"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//this.dataGridView1.Columns["PCVolAched"].HeaderText = "PC VolAched";
//// tbl_kacontractdata.NSRAched,
//this.dataGridView1.Columns["NSRAched"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["NSRAched"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//this.dataGridView1.Columns["NSRAched"].HeaderText = "NSR Ached";
//// tbl_kacontractdata.UCVolAched,
//this.dataGridView1.Columns["UCVolAched"].DefaultCellStyle.Format = "N0";
//this.dataGridView1.Columns["UCVolAched"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;// = "N0";
//this.dataGridView1.Columns["UCVolAched"].HeaderText = "UC VolAched";
//// tbl_kacontractdata.CRDDAT,
//// tbl_kacontractdata.CRDUSR,
//#endregion
Viewtable viewtbl = new Viewtable(rscustemp2, dc, "List Contracts Master Alive ", 5);// view code 1 la can viet them lenh
viewtbl.Show();
}
private void button3_Click_1(object sender, EventArgs e)
{
// kaPriodandcustomerpicker
// XmlReadMode
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "kaPriod and Customer code picker")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.CommandTimeout = 0;
View.kaPriodandcustomerpicker kaPriodpicker = new View.kaPriodandcustomerpicker();
kaPriodpicker.ShowDialog();
string priod = kaPriodpicker.priod;
string customercode = kaPriodpicker.customercode;
string fromcode = kaPriodpicker.fromcode;
string tocode = kaPriodpicker.tocode;
bool onlycust = kaPriodpicker.onlycust;
bool fromcodeto = kaPriodpicker.fromcodeto;
bool chon = kaPriodpicker.kq;
if (chon & onlycust == true)
{
#region show so da chon
var rs = from tbl_kasale in dc.tbl_kasales
where tbl_kasale.Priod == priod &&
tbl_kasale.Sold_to == double.Parse(customercode)
select new
{
tbl_kasale.Priod,
tbl_kasale.Sales_District,
tbl_kasale.Sales_District_desc,
tbl_kasale.Sales_Org,
tbl_kasale.Sold_to,
tbl_kasale.Cust_Name,
tbl_kasale.Outbound_Delivery,
tbl_kasale.Key_Acc_Nr,
tbl_kasale.Delivery_Date,
tbl_kasale.Invoice_Doc_Nr,
tbl_kasale.Invoice_Date,
tbl_kasale.Currency,
tbl_kasale.Mat_Group,
tbl_kasale.Mat_Group_Text,
tbl_kasale.Mat_Number,
tbl_kasale.Mat_Text,
PCs = tbl_kasale.EC,
tbl_kasale.UoM,
EC = tbl_kasale.PC,
tbl_kasale.UC,
tbl_kasale.Litter,
tbl_kasale.GSR,
tbl_kasale.NSR,
tbl_kasale.Username,
tbl_kasale.id
};
Viewtable viewtbl = new Viewtable(rs, dc, "SALES DATA PRIOD: " + priod + " OF CUSTOMER " + customercode, 55);// view code 1 la can viet them lenh
viewtbl.ShowDialog();
#endregion
}
if (chon & fromcodeto == true)
{
#region show so da chon
var rs = from tbl_kasale in dc.tbl_kasales
where tbl_kasale.Priod == priod &&
tbl_kasale.Sold_to >= double.Parse(fromcode) &&
tbl_kasale.Sold_to <= double.Parse(tocode)
select new
{
tbl_kasale.Priod,
tbl_kasale.Sales_District,
tbl_kasale.Sales_District_desc,
tbl_kasale.Sales_Org,
tbl_kasale.Sold_to,
tbl_kasale.Cust_Name,
tbl_kasale.Outbound_Delivery,
tbl_kasale.Key_Acc_Nr,
tbl_kasale.Delivery_Date,
tbl_kasale.Invoice_Doc_Nr,
tbl_kasale.Invoice_Date,
tbl_kasale.Currency,
tbl_kasale.Mat_Group,
tbl_kasale.Mat_Group_Text,
tbl_kasale.Mat_Number,
tbl_kasale.Mat_Text,
PCs = tbl_kasale.EC,
tbl_kasale.UoM,
EC = tbl_kasale.PC,
tbl_kasale.UC,
tbl_kasale.Litter,
tbl_kasale.GSR,
tbl_kasale.NSR,
tbl_kasale.Username,
tbl_kasale.id
};
Viewtable viewtbl = new Viewtable(rs, dc, "SALES DATA PRIOD: " + priod + " FROM CODE " + fromcode + " TO CODE " + tocode, 55);// view code 1 la can viet them lenh
viewtbl.ShowDialog();
#endregion
}
}
}
private void button4_Click(object sender, EventArgs e)
{
FormCollection fc = System.Windows.Forms.Application.OpenForms;
bool kq = false;
foreach (Form frm in fc)
{
if (frm.Text == "kaPriod and Customer code picker")
{
kq = true;
frm.Focus();
}
}
if (!kq)
{
View.kaPriodandcustomerpicker kaPriodpicker = new View.kaPriodandcustomerpicker();
// Datepick
kaPriodpicker.ShowDialog();
string priod = kaPriodpicker.priod;
string customercode = kaPriodpicker.customercode;
DateTime fromdate = kaPriodpicker.fromdate;
DateTime todate = kaPriodpicker.todate;
bool chon = kaPriodpicker.kq;
if (chon)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
#region // view sales volume
var rs33 = from tbl_kasale in dc.tbl_kasales
where tbl_kasale.Priod == priod &&
tbl_kasale.Sold_to == double.Parse(customercode) &&
tbl_kasale.Cogs != null
select new
{
tbl_kasale.Priod,
tbl_kasale.Sold_to,
tbl_kasale.Sales_Org,
tbl_kasale.Sales_District,
tbl_kasale.Sales_District_desc,
tbl_kasale.Cust_Name,
tbl_kasale.Outbound_Delivery,
tbl_kasale.Delivery_Date,
tbl_kasale.Invoice_Doc_Nr,
tbl_kasale.Invoice_Date,
tbl_kasale.Key_Acc_Nr,
///tbl_kasale.c,
tbl_kasale.Mat_Group,
tbl_kasale.Mat_Group_Text,
tbl_kasale.Mat_Number,
tbl_kasale.Mat_Text,
tbl_kasale.Currency,
PCs = tbl_kasale.EC,
tbl_kasale.UoM,
// tbl_kasalesTemp.EmptyCountValue,
COGS = tbl_kasale.Cogs,
// tbl_kasalesTemp.Litter,
// tbl_kasalesTemp.NETP,
// tbl_kasalesTemp.NSR,
// tbl_kasalesTemp.
// EC = tbl_kasalesTemp.PC,
// tbl_kasalesTemp.UC,
UserUpdate = tbl_kasale.Username,
tbl_kasale.id,
};
if (rs33.Count() >= 0)
{
Viewtable viewtbl = new Viewtable(rs33, dc, "COGS DATA PRIOD: " + priod + " OF CUSTOMER CODE " + customercode, 00);// view code 22 la can viet cogs
viewtbl.ShowDialog();
// viewtbl.Focus();
}
#endregion
}
}
}
