private static DataTable Pl_Pl(string refN, string refType)
{
string sql = string.Format(@"SELECT det.ChgDes1 + '/' + det.ChgDes2 AS Gd, mast.DocNo AS Vn, mast.SupplierBillNo AS DocN,
case when Mast.DocType='SC' then -det.LineLocAmt else det.LineLocAmt end AS Amount
FROM XAApPayable AS mast INNER JOIN XAApPayableDet AS det ON mast.SequenceId = det.DocId
WHERE (mast.MastRefNo = '{0}') AND (mast.DocType = 'PL' or mast.DocType = 'SC' or mast.DocType = 'SD') AND (mast.MastType = '{1}')", refN, refType);
DataTable dt = ConnectSql.GetTab(sql);
DataTable tab = dt.Copy();
return(tab);
}
protected void grid_DoIn_BeforePerformDataSelect(object sender, EventArgs e)
{
ASPxGridView grd = (sender as ASPxGridView) as ASPxGridView;
string sql = "select DoNo from Wh_Trans where Id='" + SafeValue.SafeInt(grd.GetMasterRowKeyValue(), 0) + "'";
string doNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "");
sql = string.Format(@"select * from Wh_Do d left join (select sum(Qty1) as TotalQty,DoNo from Wh_TransDet group by DoNo) as det on d.DoNo=det.DoNo left join Wh_Trans mast on d.PoNo=mast.DoNo and d.DoType='IN' where BlanketNo='{0}'", doNo);
DataTable tab = ConnectSql.GetTab(sql);
grd.DataSource = tab;
}
protected void grid_SubSo_BeforePerformDataSelect(object sender, EventArgs e)
{
ASPxGridView grd = (sender as ASPxGridView) as ASPxGridView;
string sql = "select DoNo from Wh_Trans where Id=" + SafeValue.SafeInt(grd.GetMasterRowKeyValue(), 0) + "";
string blanketNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "-1");
sql = string.Format(@"select mast.DoNo,DoDate,PartyName,TotalQty,DoStatus from Wh_Trans mast left join (select sum(Qty1) as TotalQty,DoNo from Wh_TransDet group by DoNo) as tab on tab.DoNo=mast.DoNo where BlanketNo='{0}' and DoType='PO'", blanketNo);
DataTable tab = ConnectSql.GetTab(sql);
grd.DataSource = tab;
}
protected void grid_SoInv_BeforePerformDataSelect(object sender, EventArgs e)
{
ASPxGridView grd = (sender as ASPxGridView) as ASPxGridView;
string sql = "select DoNo from Wh_Trans where Id='" + SafeValue.SafeInt(grd.GetMasterRowKeyValue(), 0) + "'";
string doNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "");
sql = string.Format(@"select * from XAArInvoice inv left join Wh_Trans mast on inv.MastRefNo=mast.DoNo and mast.DoType='PO' where BlanketNo='{0}'", doNo);
DataTable tab = ConnectSql.GetTab(sql);
grd.DataSource = tab;
}
public string IsClient()
{
string sql = "select Role from [User] where Name='" + EzshipHelper.GetUserName() + "'";
DataTable dt = ConnectSql.GetTab(sql);
if (dt.Rows.Count > 0)
{
return(SafeValue.SafeString(dt.Rows[0][0]));
}
return("Client");
}
private bool checkExist(string id, string code)
{
string sql = "select COUNT(*) from XXUom where CodeType='ReturnType' and Code='" + code + "' and Id<>" + id;
int result = Convert.ToInt32(ConnectSql.GetTab(sql).Rows[0][0]);
if (result > 0)
{
return(true);
}
return(false);
}
private bool add_newTrip_CheckMultiple(string Type, string JobNo, int contId)
{
bool res = true;
string sql = string.Format(@"select Id,TripCode from ctm_jobdet2 where JobNo='{0}' and Det1Id={1} and TripCode='{2}' order by Id desc", JobNo, contId, Type);
DataTable dt = ConnectSql.GetTab(sql);
if (dt.Rows.Count > 0)
{
res = false;
}
return(res);
}
public DataTable GetDetData1()
{
string no = "0";
if (Request.QueryString["no"] != null)
{
no = SafeValue.SafeString(Request.QueryString["no"]);
}
string sql = string.Format(@"select ChgCode,(-Amt) as Amt from Hr_PayrollDet where PayrollId={0} and Amt<0", no);
return(ConnectSql.GetTab(sql));
}
public DataTable GetDetData2(int person)
{
int no = 0;
if (person != null)
{
no = person;
}
string sql = string.Format(@"select sum(CPF1+CPF2) as Amt from Hr_cpf where Person={0}", no);
return(ConnectSql.GetTab(sql));
}
public DataTable GetTrip()
{
DataTable tab = null;
if (Request.QueryString["no"] != null)
{
string no = Request.QueryString["no"].ToString();
string sql = string.Format(@"select TripIndex,TripCode,BillingRemark,ContainerNo,Statuscode,RequestVehicleType,FromCode,ToCode from ctm_jobdet2 where JobNo='{0}'", no);
tab = ConnectSql.GetTab(sql);
}
return(tab);
}
protected void Page_Load(object sender, EventArgs e)
{
string type = SafeValue.SafeString(Request.QueryString["type"]);
string sql = string.Format(@"select tab_mast.DoNo,tab_mast.DoDate,TotalQty,TotalQty-isnull(tab_a.Qty,0) as RemainingQty,PartyName from Wh_Trans tab_mast
inner join (select SUM(Qty1) as TotalQty,DoNo from Wh_TransDet group by DoNo) tab_det on tab_det.DoNo=tab_mast.DoNo
left join (select BlanketNo,SUM(Qty1) as Qty,MAX(mast.DoNo) as DoNo from Wh_Trans mast inner join Wh_TransDet det on mast.DoNo=det.DoNo group by BlanketNo) as tab_a on tab_a.BlanketNo=tab_mast.DoNo
where tab_mast.DoType='{0}'", type);
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
}
protected void btn_search_Click(object sender, EventArgs e)
{
string where = "";
string userId = HttpContext.Current.User.Identity.Name;
string sql = string.Format(@"select *,dbo.fun_GetPartyName(d.PartyId) as PartyName,
(select SUM(Qty1) from Wh_TransDet det where det.DoNo=d.DoNo) as TotalQty,
(select SUM(DocAmt) from Wh_TransDet det where det.DoNo=d.DoNo) as TotalAmt,
isnull((select sum(det1.Qty5) from Wh_DoDet det1 inner join Wh_DO do on do.PoNo=d.DoNo and do.DoNo=det1.DoNo where det1.DoType='OUT'),0) as PickQty,
isnull((select sum(det1.Qty1) from Wh_DoDet det1 inner join Wh_DO do on do.PoNo=d.DoNo and do.DoNo=det1.DoNo where det1.DoType='OUT'),0) as ShipQty,
isnull((select cast(sum(inv.Qty) as numeric(10,0)) from XAArInvoiceDet inv inner join Wh_TransDet det on REPLACE(inv.ChgCode,'(SKU)','')=det.ProductCode and det.DoType='SO' and inv.MastRefNo=det.DoNo where inv.DocType='IV' and inv.MastRefNo=d.DoNo),0) as BillQty
from Wh_Trans d");
string dateFrom = "";
string dateTo = "";
if (txt_from.Value != null && txt_end.Value != null)
{
dateFrom = txt_from.Date.ToString("yyyy-MM-dd");
dateTo = txt_end.Date.AddDays(1).ToString("yyyy-MM-dd");
}
if (txt_PoNo.Text.Trim() != "")
{
where = GetWhere(where, "d.DoNo='" + txt_PoNo.Text.Trim() + "'");
}
else if (this.txt_CustId.Text.Length > 0)
{
where = GetWhere(where, "PartyId='" + this.txt_CustId.Text.Trim() + "'");
}
if (dateFrom.Length > 0 && dateTo.Length > 0)
{
where = GetWhere(where, " DoDate >= '" + dateFrom + "' and DoDate < '" + dateTo + "'");
}
//if (cmb_Type.Text.Trim() != "")
//{
// where = GetWhere(where, " DoStatus = '" + cmb_Type.Text + "'");
//}
if (userId == "ADMIN")
{
where = GetWhere(where, " d.SalesId<>'" + userId + "'");
}
if (userId != "ADMIN")
{
where = GetWhere(where, " d.SalesId='" + userId + "'");
}
if (where.Length > 0)
{
sql += " where " + where + " and d.DoType='SO' order by d.DoNo";
}
//throw new Exception(sql);
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
}
public DataTable GetCont()
{
DataTable tab = null;
if (Request.QueryString["no"] != null)
{
string no = Request.QueryString["no"].ToString();
string sql = string.Format(@"select ContainerNo,ContainerType,Weight,ScheduleDate,StatusCode from ctm_jobdet1 where JobNo='{0}'", no);
tab = ConnectSql.GetTab(sql);
}
return(tab);
}
protected void grid_Po_BeforePerformDataSelect(object sender, EventArgs e)
{
ASPxGridView grd = (sender as ASPxGridView) as ASPxGridView;
string sql = "select LcNo from Ref_LC where Id='" + SafeValue.SafeInt(grd.GetMasterRowKeyValue(), 0) + "'";
string doNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "");
sql = string.Format(@"select *,dbo.fun_GetPartyName(PartyId) as PartyName,(select SUM(Qty1) from Wh_TransDet det where det.DoNo=d.DoNo) as TotalQty,
(select SUM(DocAmt) from Wh_TransDet det where det.DoNo=d.DoNo) as TotalAmt from Wh_Trans d where LcNo='{0}' and d.DoType='PO'", doNo);
DataTable tab = ConnectSql.GetTab(sql);
grd.DataSource = tab;
}
private void CreateInv(string invN, int id, string docId)
{
string sql = string.Format(@"select * from job_cost where Id={0}", id);
DataTable dt = ConnectSql.GetTab(sql);
string sql_part1 = string.Format(@"insert into XAArInvoiceDet (DocId,DocNo,DocType,DocLineNo,AcCode,AcSource,ChgCode,ChgDes1,
GstType,Qty,Price,Unit,Currency,ExRate,Gst,GstAmt,DocAmt,LocAmt,LineLocAmt,MastRefNo,JobRefNo,MastType,JobCostId,ChgDes4)
values");
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = "";
decimal qty = SafeValue.SafeDecimal(dt.Rows[i]["Qty"]);
string chgCodeId = SafeValue.SafeString(dt.Rows[i]["ChgCode"]);
string chgCodeDes = SafeValue.SafeString(dt.Rows[i]["ChgCodeDes"]);
decimal price = SafeValue.SafeDecimal(dt.Rows[i]["Price"]);
string cntNo = SafeValue.SafeString(dt.Rows[i]["ContNo"]);
string cntType = SafeValue.SafeString(dt.Rows[i]["ContType"]);
string jobNo = SafeValue.SafeString(dt.Rows[i]["JobNo"]);
string billType = "";
string remark = SafeValue.SafeString(dt.Rows[i]["Remark"]);
string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes,GstP,GstTypeId,ChgTypeId from XXChgCode where chgCodeId='{0}'", chgCodeId);
DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode);
decimal gst = 0;
string gstType = "";
string chgTypeId = "";
if (dt_chgCode.Rows.Count > 0)
{
gst = SafeValue.SafeDecimal(dt_chgCode.Rows[0]["GstP"]);
gstType = SafeValue.SafeString(dt_chgCode.Rows[0]["GstTypeId"]);
chgTypeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgTypeId"]);
chgCodeDes = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgCodeDes"]);
}
decimal amt = SafeValue.ChinaRound(qty * SafeValue.SafeDecimal(price, 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(gst, 0)), 2);
decimal docAmt = amt + gstAmt;
decimal locAmt = SafeValue.ChinaRound(docAmt * SafeValue.SafeDecimal(1, 1), 2);
if (chgCodeDes.ToUpper().Equals("TRUCKING"))
{
chgCodeDes += " " + billType.ToUpper();
}
string sql_part2 = string.Format(@"('{0}','{1}','IV','{2}','','CR','{3}','{4}','{14}',1,{5},'{9}','SGD',1,{10},{11},{12},{13},0,'{6}','{7}','{8}',{15},'{16}') select @@IDENTITY ", docId, invN, i + 1, chgCodeId, chgCodeDes, price, jobNo, cntNo, chgTypeId, cntType, gst, gstAmt, docAmt, locAmt, gstType, id, remark);
sql += sql.Length > 0 ? "," + sql_part2 : sql_part2;
}
if (sql.Length > 0)
{
sql = sql_part1 + sql;
int re = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
UpdateMaster(SafeValue.SafeInt(docId, 0));
UpdateLineId(id, re);
}
}
public List <det1_row> GetContainerByDate(string date)
{
//string[] temp = date.Split('/');
//if (temp.Length != 3)
//{
// return null;
//}
//string temp_d = temp[2] + "-" + temp[1] + "-" + temp[0];
string sql = string.Format(@"with tab1 as (
select ROW_NUMBER() over(order by Det1Id,ContainerNo,case StageCode when 'Pending' then 1 when 'Port' then 2 when 'Park1' then 3 when 'Warehouse' then 4 when 'Park2' then 5 when 'Yard' then 6 when 'Completed' then 7 else 0 end,convert(nvarchar,FromDate,111),FromTime) as RowNum
,JobNo,ContainerNo,StageCode,StageStatus,DriverCode,FromDate,FromTime,ChessisCode,Remark,Det1Id,Id as Det2Id,LoadCode,Carpark
from CTM_JobDet2
where datediff(d,FromDate,'{0}')<=0
),
tab2 as (
select distinct Det1Id,ContainerNo from CTM_JobDet2 where datediff(d,FromDate,'{0}')=0
),
tab3 as (
select max(tab1.RowNum) as RowNum from tab2 left outer join tab1 on tab2.Det1Id=tab1.Det1Id and tab2.ContainerNo=tab1.ContainerNo group by tab2.Det1Id,tab2.ContainerNo
),
tab4 as (
select tab1.*,det1.ContainerType,(case job.JobType when 'KD-IMP' then 'I' when 'KD-EXP' then 'E' when 'FCL-IMP' then 'I' when 'FCL-EXP' then 'E' else 'L' end) as JobType
from tab3 left outer join tab1 on tab3.RowNum=tab1.RowNum
left outer join CTM_JobDet1 as det1 on tab1.Det1Id=det1.Id
left outer join CTM_Job as job on tab1.JobNo=job.JobNo
where DATEDIFF(d,FromDate,'{0}')=0
)
select * from tab4 order by FromTime", date);
DataTable dt = ConnectSql.GetTab(sql);
List <det1_row> list = new List <det1_row>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
det1_row newRow = new det1_row();
newRow.Det2Id = dr["Det2Id"].ToString();
newRow.JobNo = dr["JobNo"].ToString();
newRow.JobType = dr["JobType"].ToString();
newRow.ContainerNo = dr["ContainerNo"].ToString();
newRow.ContainerType = dr["ContainerType"].ToString();
newRow.StageCode = dr["StageCode"].ToString();
newRow.StageStatus = dr["StageStatus"].ToString();
newRow.Time = dr["FromTime"].ToString();
newRow.Remark = dr["Remark"].ToString();
newRow.Driver = dr["DriverCode"].ToString();
newRow.Trailer = dr["ChessisCode"].ToString();
newRow.Load = dr["LoadCode"].ToString();
newRow.Carpark = dr["Carpark"].ToString();
list.Add(newRow);
}
return(list);
}
public static DataTable dsProfitByCust(string refType, string type, DateTime d1, DateTime d2, string custId, string userName)
{
string role = EzshipHelper.GetUseRole();
string exceptSales = System.Configuration.ConfigurationManager.AppSettings["ExceptSales"].ToUpper();
string sql = "";
if (type != "E")
{
sql = string.Format(@"SELECT ref.RefNo, ref.JobType as RefType, ref.Pol as Port, ref.ExRate,convert(nvarchar,ref.Eta,103) as Eta,convert(nvarchar,ref.Etd,103) as Etd,
job.JobNo, xxpPP.SalesmanId as Salesman, job.HblNo as Hbl, cast(isnull(job.Weight,0) as numeric(21,3)) as Wt, cast(isnull(job.Volume,0) as numeric(21,3)) as M3,ref.Volume as TotVolume,
'Date From {0} to {1}' as PeriodStr,'Import' as ImpExpInd,case when LEN(job.CustomerId)>0 then '('+job.CustomerId+')'+isnull(xxpPP.Name,'') else 'NA' end as Cust
,dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','CN')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','PL')- dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','SC')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','SD')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','VO') as CostAmt
,dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','IV')+dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','DN') as RevAmt
,dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','IV')+dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','DN')-(dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','CN')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','PL')-dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','SC')+dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','SD')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'si','VO')) as ProfitAmt
,'{4}' as UserId
FROM SeaImport AS job INNER JOIN SeaImportRef AS ref ON job.RefNo = ref.RefNo
left outer join XXParty as xxpPP on job.CustomerId=xxpPP.PartyId
WHERE ref.StatusCode<>'CNL' and (ref.Eta >= '{6}') AND (ref.Eta < '{7}')
and (case when '{2}'='SALESMANAGER' and len('{3}')>0 and xxpPP.SalesmanId='{3}' then 0
when '{2}'='SALESSTAFF' and xxpPP.SalesmanId<>'{4}' then 0
else 1 end )=1", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"), role, exceptSales, userName, type, d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"));
}
else
{
sql = string.Format(@"SELECT ref.RefNo, ref.JobType as RefType, ref.Pod as Port, ref.ExRate,convert(nvarchar,ref.Eta,103) as Eta,convert(nvarchar,ref.Etd,103) as Etd,
job.JobNo, xxpPP.SalesmanId as Salesman, job.HblNo as Hbl, cast(isnull(job.Weight,0) as numeric(21,3)) as Wt, cast(isnull(job.Volume,0) as numeric(21,3)) as M3,ref.Volume as TotVolume
,'Date From {0} to {1}' as PeriodStr, 'Export' as ImpExpInd,case when LEN(job.CustomerId)>0 then '('+job.CustomerId+')'+isnull(xxpPP.Name,'') else 'NA' end as Cust
,dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','CN')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','PL')- dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','SC')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','SD')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','VO') as CostAmt
,dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','IV')+dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','DN') as RevAmt
,dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','IV')+dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','DN')-(dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','CN')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','PL')- dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','SC')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','SD')+ dbo.Fun_GetBillAmt(job.RefNo,job.JobNo,'SE','VO')) as ProfitAmt
,'{4}' as UserId
FROM SeaExport AS job INNER JOIN SeaExportRef AS ref ON job.RefNo = ref.RefNo
left outer join XXParty as xxpPP on job.CustomerId=xxpPP.PartyId
WHERE ref.StatusCode<>'CNL' and (ref.Etd >= '{6}') AND (ref.Etd < '{7}')and
(case when '{2}'='SALESSTAFF' and '{4}'<>xxpPP.Salesmanid then 0
else 1 end )=1 and (ref.RefType LIKE '{8}%')", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"), role, exceptSales, userName, type, d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"), refType.Replace("null", ""));
}
if (custId == "NA")
{
}
else
{
sql += " and Job.CustomerId='" + custId + "'";
}
sql += " order by job.JobNo";
DataTable dt = ConnectSql.GetTab(sql);
dt.TableName = "Det";
return(dt);
}
protected void btn_search_Click(object sender, EventArgs e)
{
DateTime dt_from = SafeValue.SafeDate(search_from.Date, new DateTime(1900, 1, 1));
DateTime dt_to = SafeValue.SafeDate(search_to.Date, DateTime.Now);
string sql = string.Format(@"select * from CTM_JobEventLog
where JobType='PSA' and datediff(d,CreateDateTime,'{0}')<=0 and datediff(d,CreateDatetime,'{1}')>=0
order by Id desc", dt_from, dt_to);
DataTable dt = ConnectSql.GetTab(sql);
gv.DataSource = dt;
gv.DataBind();
}
private bool check_Trip_Status(string id, string driverCode, string status)
{
if (status == "S" || status == "P")
{
string sql = string.Format(@"select COUNT(*) from CTM_JobDet2 where DriverCode='{0}' and Statuscode='{2}' and Id<>'{1}'", driverCode, id, status);
int result = SafeValue.SafeInt(ConnectSql.GetTab(sql).Rows[0][0], 0);
if (result > 0)
{
return(true);
}
}
return(false);
}
private void BindData()
{
string sql = @" select * from (
select PartyId,Code,Name,WarningAmt,WarningQty,BlockQty,BlockAmt
,(select count(sequenceId) from XAArInvoice where PartyTo=XXParty.PartyId and BalanceAmt>0) as Cnt
,(SELECT CAST(sum(BalanceAmt*exRate) AS DECIMAL(20,2)) from XAArInvoice where PartyTo=XXParty.PartyId and BalanceAmt>0) as Amt
,Status,Case when upper(isnull(Status,''))='INACTIVE' then 'UnLock' when upper(isnull(Status,''))='USE' then 'Lock' ELSE '' END as BtnTxt from XXParty
) as bb where upper(isnull(Status,''))='INACTIVE' Or cnt>WarningQty OR CNT>BlockQty or Amt>WarningAmt or Amt>BlockAmt";
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
}
protected void FillToLocationCombo(ASPxComboBox cmb, string wh)
{
if (string.IsNullOrEmpty(wh))
{
return;
}
cmb.Items.Clear();
string sql = string.Format(@"select Code,WarehouseCode from ref_location");
DataTable tab = ConnectSql.GetTab(sql);
dsRefLocation.FilterExpression = string.Format("WarehouseCode = '{0}'", wh);
cmb.DataBind();
}
public DataTable GetData()
{
string no = "0";
if (Request.QueryString["no"] != null)
{
no = SafeValue.SafeString(Request.QueryString["no"]);
}
string sql = string.Format(@"select l.*,p.Name,p.Department,tmp.Days as Entitled,(CONVERT(int, tmp.Days)-CONVERT(int, l.Days)) as BalDays,tmp.Remark as TemRemark from Hr_Leave l left join Hr_Person p on l.ApproveBy=p.Id
left join Hr_LeaveTmp tmp on tmp.Person=l.Person and tmp.LeaveType=l.LeaveType where l.Id={0}", no);
return(ConnectSql.GetTab(sql));
}
private void UpdateMaster(int docId)
{
string sql = string.Format("update XaArInvoiceDet set LineLocAmt=locAmt* (select ExRate from XAArInvoice where SequenceId=XaArInvoiceDet.docid) where DocId='{0}'", docId);
C2.Manager.ORManager.ExecuteCommand(sql);
sql = string.Format("select sum(LocAmt) as DocAmt,sum(lineLocAmt) as LocAmt from XaArInvoiceDet where DocId='{0}'", docId);
DataTable tab = ConnectSql.GetTab(sql);
if (tab.Rows.Count == 1)
{
sql = string.Format("Update XAArInvoice set DocAmt='{0}',BalanceAmt='{0}',LocAmt='{1}' where SequenceId={2}", SafeValue.SafeDecimal(tab.Rows[0]["DocAmt"]), SafeValue.SafeDecimal(tab.Rows[0]["LocAmt"]), docId);
C2.Manager.ORManager.ExecuteCommand(sql);
}
}
public static DataTable PrintDN(string refN, string jobN)
{
DataTable ds = new DataTable();
try
{
string sqlstr = string.Format(@"exec proc_PrintDN '{0}','{1}','{2}','{3}','{4}'", refN, jobN, "AI", "", System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
ds = ConnectSql.GetTab(sqlstr);
}
catch (Exception ex)
{
}
return(ds);
}
protected void btn_search_Click(object sender, EventArgs e)
{
string sql = string.Format(@"with pri as (
select * from ctm_MastData where [Type]='tripcode'
),
tb1 as (
select det2.Id,det2.JobNo,det2.ContainerNo,det2.Incentive1,det2.Incentive2,det2.Incentive3,det2.Incentive4,det2.Charge1,det2.Charge2,det2.Charge3,det2.Charge4,det2.Charge5,det2.Charge6,det2.Charge7,det2.Charge8,det2.Charge9,det2.Charge10,det1.SealNo,det1.ContainerType,det1.ScheduleDate,det2.FromDate,det2.FromTime,det2.ToDate,det2.ToTime,det2.TripCode,det2.TowheadCode,det2.Overtime,det2.OverDistance,
det2.ChessisCode,det2.FromCode,det2.ToCode,job.JobType,det2.ParkingZone,
isnull((select top 1 case dri.ServiceLevel when 'Level1' then pri.Price1 when 'Level2' then pri.Price2 when 'Level3' then pri.Price3 else 0 end from pri where pri.Code=det2.TripCode),0) as TripCodePrice,
isnull((select top 1 case dri.ServiceLevel when 'Level1' then pri.Price1 when 'Level2' then pri.Price2 when 'Level3' then pri.Price3 else 0 end from pri where pri.Code=det2.Overtime),0) as OverTimePrice,
case when det2.OverDistance='Y' then isnull((select top 1 case dri.ServiceLevel when 'Level1' then pri.Price1 when 'Level2' then pri.Price2 when 'Level3' then pri.Price3 else 0 end from pri where pri.Code='OJ'),0) else 0 end as QJPrice,(select top 1 PayNo from XAApPaymentDet where JobRefNo=det2.Id) as DocNo,(select count(*) from XAApPaymentDet where JobRefNo=det2.Id) as Cnt
from CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
left outer join CTM_Job as job on job.jobNo=det2.JobNo
left outer join CTM_Driver as dri on dri.Code=det2.DriverCode
");
string sql_part1 = string.Format(@")
select *,TripCodePrice+OverTimePrice+QJPrice as Total,isnull(Charge1,0)+isnull(Charge2,0)+isnull(Charge3,0)+isnull(Charge4,0)+isnull(Charge5,0)+isnull(Charge6,0)+isnull(Charge7,0)+isnull(Charge8,0)+isnull(Charge9,0)+isnull(Charge10,0) as TotalCharge from tb1 where isnull(Charge1,0)+isnull(Charge2,0)+isnull(Charge3,0)+isnull(Charge4,0)+isnull(Charge5,0)+isnull(Charge6,0)+isnull(Charge7,0)+isnull(Charge8,0)+isnull(Charge9,0)+isnull(Charge10,0)>0");
if (search_DateFrom.Date < new DateTime(1900, 1, 1))
{
search_DateFrom.Date = DateTime.Now.AddDays(-15);
}
if (search_DateTo.Date < new DateTime(1900, 1, 1))
{
search_DateTo.Date = DateTime.Now.AddDays(8);
}
string where = string.Format(@" where det2.Statuscode='C' and DATEDIFF(d,det2.ToDate,'{0}')<=0 and DATEDIFF(d,det2.ToDate,'{1}')>=0", search_DateFrom.Date, search_DateTo.Date);
if (search_Driver.Text.Trim().Length > 0)
{
where += string.Format(@" and det2.DriverCode='{0}'", search_Driver.Text);
}
if (search_TowheadCode.Text.Trim().Length > 0)
{
where += string.Format(@" and det2.TowheadCode='{0}'", search_TowheadCode.Text);
}
if (cbb_Trip_TripCode.Text.Trim() != "")
{
where += string.Format(@" and det2.TripCode='{0}'", cbb_Trip_TripCode.Text);
}
if (cbb_zone.Text.Trim() != "")
{
where += string.Format(@" and det2.ParkingZone='{0}'", cbb_zone.Text);
}
string new_sql = sql + where + sql_part1;
grid_Transport.DataSource = ConnectSql.GetTab(new_sql);
grid_Transport.DataBind();
}
protected void btn_search_Click(object sender, EventArgs e)
{
string sql1 = "select Role,CustId,xxp.Name from [User] as u left outer join XXParty as xxp on CustId=PartyId where u.Name='" + EzshipHelper.GetUserName() + "'";
DataTable dt1 = ConnectSql.GetTab(sql1);
string Role = "";
if (dt1.Rows.Count > 0)
{
Role = SafeValue.SafeString(dt1.Rows[0][0]);
}
string sql = string.Format(@"select job.JobNo,job.JobDate,det1.ContainerNo,det1.ContainerType,job.EtaDate,job.EtdDate,job.Pol,job.Pod,job.Vessel,job.Voyage,job.PickupFrom,job.DeliveryTo,det1.ScheduleDate,det1.StatusCode ,(select top 1 code from XXParty where PartyId=job.ClientId) as client,(select top 1 code from XXParty where PartyId=job.HaulierId) as Haulier,job.Terminalcode,job.JobType, case when '" + Role + @"'='Client' and job.statuscode<>'USE' then 'false' else '0' end as Role
from CTM_Job as job
left outer join CTM_JobDet1 as det1 on job.JobNo=det1.JobNo
where job.JobNo like '%{0}%' and isnull(det1.ContainerNo,'') like '%{1}%' ", txt_search_jobNo.Text, txt_search_ContNo.Text);
if (txt_search_dateFrom.Date > new DateTime(1900, 1, 1))
{
sql += " and datediff(d,'" + txt_search_dateFrom.Date + "',job.jobDate)>=0";
}
if (txt_search_dateTo.Date > new DateTime(1900, 1, 1))
{
sql += " and datediff(d,'" + txt_search_dateTo.Date + "',job.jobDate)<=0";
}
if (cbb_StatusCode.Text != "All")
{
sql += " and det1.StatusCode='" + cbb_StatusCode.Text + "'";
}
if (btn_ClientId.Text.Length > 0)
{
sql += " and job.clientId='" + btn_ClientId.Text + "'";
}
if (search_JobType.Text.Length > 0)
{
sql += " and job.JobType='" + search_JobType.Text + "'";
}
//string sql1 = "select Role,CustId,xxp.Name from [User] as u left outer join XXParty as xxp on CustId=PartyId where u.Name='" + EzshipHelper.GetUserName() + "'";
//DataTable dt1 = ConnectSql.GetTab(sql1);
if (dt1 != null && dt1.Rows.Count > 0 && dt1.Rows[0][0].ToString().Equals("Client"))
{
sql += " and ClientId='" + dt1.Rows[0][1] + "'";
btn_ClientId.Value = dt1.Rows[0][1];
txt_ClientName.Text = SafeValue.SafeString(dt1.Rows[0][2]);
btn_ClientId.Enabled = false;
}
sql += " order by job.JobDate desc";
DataTable dt = ConnectSql.GetTab(sql);
this.grid_Transport.DataSource = dt;
this.grid_Transport.DataBind();
}
private bool add_newTrip_CheckMultiple(DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e, string Type, string jobNo, string contId)
{
bool res = false;
string sql = string.Format(@"select Id,TripCode from ctm_jobdet2 where JobNo='{0}' and Det1Id={1} and TripCode='{2}' order by Id desc", jobNo, contId, Type);
DataTable dt = ConnectSql.GetTab(sql);
if (dt.Rows.Count > 0)
{
//throw new Exception("Exist trip:" + Type);
e.Result = "Save Error:Exist trip " + Type;
res = true;
}
return(res);
}
protected void grid_Active_BeforePerformDataSelect(object sender, EventArgs e)
{
ASPxGridView grd = (sender as ASPxGridView) as ASPxGridView;
string sql = "select PartyId from XXParty where SequenceId='" + SafeValue.SafeInt(grd.GetMasterRowKeyValue(), 0) + "'";
string bankCode = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "");
sql = string.Format(@"select lc.*,(WarningAmt-LcAmount-isnull(tab_lc.TotalLc,0)) as RemainingLimit from Ref_LC lc
left join (select sum(LcAmount) as TotalLc,max(LcAppDate) LcAppDate,max(LcNo) as LcNo from Ref_LC where BankCode='UOM' and StatusCode='Buy' group by BankCode,LcNo) as tab_lc
on tab_lc.LcAppDate<lc.LcAppDate
inner join XXParty p on lc.BankCode=p.PartyId where lc.StatusCode='Buy' and BankCode='{0}' order by LcAppDate asc", bankCode);
DataTable tab = ConnectSql.GetTab(sql);
grd.DataSource = tab;
}
protected void btn_search_Click(object sender, EventArgs e)
{
string type = "";
if (Request.QueryString["type"] != null)
{
type = SafeValue.SafeString(Request.QueryString["type"]);
}
string where = "";
string sql = string.Format(@"select * from JobInfo");
string dateFrom = "";
string dateTo = "";
if (txt_from.Value != null && txt_end.Value != null)
{
dateFrom = txt_from.Date.ToString("yyyy-MM-dd");
dateTo = txt_end.Date.AddDays(1).ToString("yyyy-MM-dd");
}
if (txt_PoNo.Text.Trim() != "")
{
where = GetWhere(where, "JobNo='" + txt_PoNo.Text.Trim() + "'");
}
else if (this.txt_CustId.Text.Length > 0)
{
where = GetWhere(where, "CusromerId='" + this.txt_CustId.Text.Trim() + "'");
}
if (dateFrom.Length > 0 && dateTo.Length > 0)
{
where = GetWhere(where, " JobDate >= '" + dateFrom + "' and JobDate < '" + dateTo + "'");
}
if (type != "")
{
where = GetWhere(where, " JobStatus = '" + type + "'");
}
if (where.Length > 0)
{
sql += " where " + where + " order by JobNo";
}
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
}
protected void btn_search_Click(object sender, EventArgs e)
{
string where = "";
//string sql = string.Format(@"select * from JobSchedule ");
string dateFrom = "";
string dateTo = "";
string surv = S.Text(cmb_SurveyId.Value);
if (txt_from.Value != null && txt_end.Value != null)
{
dateFrom = txt_from.Date.ToString("yyyy-MM-dd");
dateTo = txt_end.Date.AddDays(0).ToString("yyyy-MM-dd");
txt_date.Date = txt_from.Date;
txt_date2.Date = txt_end.Date;
}
string addon1 = "";
string addon2 = "";
if (surv.Trim().Length > 1)
{
addon1 = "value2='" + surv + "' and ";
}
if (surv.Trim().Length > 1)
{
addon2 = "partyid='" + surv + "' and ";
}
string sql = string.Format(@" select * from ( ( select QuotationNo, Value2, DateTime2, CustomerName, Contact, Tel, OriginAdd, DestinationAdd from JobInfo ");
if (txt_from.Value != null && txt_end.Value != null)
{
dateFrom = txt_from.Date.ToString("yyyy-MM-dd");
dateTo = txt_end.Date.AddDays(1).ToString("yyyy-MM-dd");
where = GetWhere(where, " Value2<>'NA' and DateTime2>= '" + dateFrom + "' and DateTime2< '" + dateTo + "'");
}
if (where.Length > 0)
{
sql += " where " + addon1 + " " + where + " and len(QuotationNo)>0 "; // + " and JobStage='Job Confirmation' order by JobNo";
}
sql += @" ) Union ( SELECT TaskType AS QuotationNo, PartyId AS Value2, DueDate AS DateTime2, TaskName AS CustomerName, ' ' AS Contact, ' ' AS Tel, Description AS OriginAdd, ' ' AS DestinationAdd FROM Tasks
where " + addon2 + " StatusCode<>'Cancel' and DueDate>= '" + dateFrom + "' and DueDate< '" + dateTo + "') ) as tempT order by DateTime2";
//dsJobSchedule.FilterExpression = where;
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
}